Lereveme Lereveme - 1 month ago 9
SQL Question

TSQL First Non-Null value in Audit Table

I have a table in SQL Server 2014 that has the following data:

ID ChangeDate FName MName LName Revision
1 2016-01-03 00:00:00.000 Ed NULL NULL 3
1 2016-01-02 00:00:00.000 NULL David NULL 2
1 2016-01-01 00:00:00.000 Joe NULL NULL 1
2 2016-01-03 00:00:00.000 Michael NULL NULL 2
2 2016-01-02 00:00:00.000 Henry Jake Smith 1


I need a tsql query that generates a single row with the most current output for each column:

ID FName MName LName
1 Ed David NULL
2 Michael Jake Smith

Answer
WITH
    sorted AS
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY id
                               ORDER BY CASE WHEN FName IS NULL THEN 1 ELSE 0 END,
                                        Revision DESC
                          ) AS FNameOrdinal,
        ROW_NUMBER() OVER (PARTITION BY id
                               ORDER BY CASE WHEN MName IS NULL THEN 1 ELSE 0 END,
                                        Revision DESC
                          ) AS MNameOrdinal,
        ROW_NUMBER() OVER (PARTITION BY id
                               ORDER BY CASE WHEN LName IS NULL THEN 1 ELSE 0 END,
                                        Revision DESC
                          ) AS LNameOrdinal,
        *
    FROM
        yourTable
)
SELECT
    id,
    MAX(CASE WHEN FNameOrdinal = 1 THEN FName ELSE NULL END)   AS FName,
    MAX(CASE WHEN MNameOrdinal = 1 THEN MName ELSE NULL END)   AS MName,
    MAX(CASE WHEN LNameOrdinal = 1 THEN LName ELSE NULL END)   AS LName
FROM
    sorted
GROUP BY
    id
Comments