Last Non-NULL Value

Question

In the below table I am attempting to fill in NULL values with the last known non-null value if the current value is null. If the first value is NULL it should remain NULL:

EntryDate   CustId  Stat    Desk    BkGrp   CyGrp   NextDate    Amt
5/9/2018    19001   PLX     H6      NULL    NULL    NULL        NULL
5/31/2018   19001   NULL    NULL    0       13      6/17/2018   1063.51
6/4/2018    19001   NULL    B98     0       13      6/17/2018   1013.51
6/21/2018   19001   PLY     NULL    NULL    NULL    7/17/2018   1033.15
7/18/2018   19001   NULL    NULL    0       13      7/17/2018   1058.15
7/22/2018   19001   NULL    NULL    1       13      8/17/2018   1077.91
1/29/2016   19503   PLZ     H6      NULL    NULL    NULL        NULL
5/31/2018   19503   NULL    NULL    1       5       6/6/2018    541.11
6/7/2018    19503   NULL    NULL    1       NULL    6/6/2018    576.11
6/10/2018   19503   PLA     NULL    2       5       7/6/2018    589.3
7/8/2018    19503   PLA     NULL    NULL    5       7/6/2018    624.3
7/11/2018   19503   NULL    NULL    3       NULL    8/6/2018    639.84

I am trying to create this:

EntryDate   CustId  Stat    Desk    BkGrp   CyGrp   NextDate    Amt
5/9/2018    19001   PLX     H6      NULL    NULL    NULL        NULL
5/31/2018   19001   PLX     H6      0       13      6/17/2018   1063.51
6/4/2018    19001   PLX     B98     0       13      6/17/2018   1013.51
6/21/2018   19001   PLY     B98     0       13      7/17/2018   1033.15
7/18/2018   19001   PLY     B98     0       13      7/17/2018   1058.15
7/22/2018   19001   PLY     B98     1       13      8/17/2018   1077.91
1/29/2016   19503   PLZ     H6      NULL    NULL    NULL        NULL
5/31/2018   19503   PLZ     H6      1       5       6/6/2018    541.11
6/7/2018    19503   PLZ     H6      1       5       6/6/2018    576.11
6/10/2018   19503   PLA     H6      2       5       7/6/2018    589.3
7/8/2018    19503   PLA     H6      2       5       7/6/2018    624.3
7/11/2018   19503   PLA     H6      3       5       8/6/2018    639.84

I have looked up various methods including The Last non NULL Puzzle but can't get anything to work across 6 columns with changing data.

This is on SQL Server 2012.

Answer 1

You can use subqueries to get the latest not null value like:

SELECT t1.entrydate,
       t1.custid,
       (SELECT TOP 1
               t2.stat
               FROM elbat t2
               WHERE t2.custid = t1.custid
                     AND t2.entrydate <= t1.entrydate
                     AND t2.stat IS NOT NULL
               ORDER BY t2.entrydate DESC) stat,
       ...
       (SELECT TOP 1
               t2.amt
               FROM elbat t2
               WHERE t2.custid = t1.custid
                     AND t2.entrydate <= t1.entrydate
                     AND t2.amt IS NOT NULL
               ORDER BY t2.entrydate DESC) amt
       FROM elbat t1
       ORDER BY t1.custid,
                t1.entrydate;

If (custid, entrydate) isn't unique -- i.e. there are more possible latest values -- you might want to find an other second criteria to ORDER BY. Like it stands it'd randomly get any of the latest (not null) values in such a case.

Answer 2

This cursor-based method does the job, though you end up with row-by-row processing, of course. Not my favorite, but that many subqueries (the other simple answer) makes my teeth itch. I have included a data source table and data output.

CREATE TABLE #t (EntryDate datetime, CustId int, Stat varchar(6), Desk varchar(4), BkGrp int, CyGrp int, NextDate datetime, Amt DECIMAL(9,2));
INSERT INTO #t VALUES('5/9/2018', 19001, 'PLX', 'H6', NULL, NULL, NULL, NULL);
INSERT INTO #t VALUES('5/31/2018', 19001, NULL, NULL, 0, 13, '6/17/2018', 1063.51);
INSERT INTO #t VALUES('6/4/2018', 19001, NULL, 'B98', 0, 13, '6/17/2018', 1013.51);
INSERT INTO #t VALUES('6/21/2018', 19001, 'PLY', NULL, NULL, NULL, '7/17/2018', 1033.15);
INSERT INTO #t VALUES('7/18/2018', 19001, NULL, NULL, 0, 13, '7/17/2018', 1058.15);
INSERT INTO #t VALUES('7/22/2018', 19001, NULL, NULL, 1, 13, '8/17/2018', 1077.91);
INSERT INTO #t VALUES('1/29/2016', 19503, 'PLZ', 'H6', NULL, NULL, NULL, NULL);
INSERT INTO #t VALUES('5/31/2018', 19503, NULL, NULL, 1, 5, '6/6/2018', 541.11);
INSERT INTO #t VALUES('6/7/2018', 19503, NULL, NULL, 1, NULL, '6/6/2018', 576.11);
INSERT INTO #t VALUES('6/10/2018', 19503, 'PLA', NULL, 2, 5, '7/6/2018', 589.3);
INSERT INTO #t VALUES('7/8/2018', 19503, 'PLA', NULL, NULL, 5, '7/6/2018', 624.3);
INSERT INTO #t VALUES('7/11/2018', 19503, NULL, NULL, 3, NULL, '8/6/2018', 639.84);


CREATE TABLE #tmp (EntryDate datetime, CustId int, Stat varchar(6), Desk varchar(4), BkGrp int, CyGrp int, NextDate datetime, Amt DECIMAL(9,2));


DECLARE cx CURSOR FOR
    SELECT EntryDate,
        CustId,
        Stat,
        Desk,
        BkGrp,
        CyGrp,
        NextDate,
        Amt
FROM #t
ORDER BY CustId, EntryDate;

DECLARE @edate datetime,
    @custId Int,
    @Stat varchar(6), 
    @Desk varchar(4), 
    @BkGrp int, 
    @CyGrp int, 
    @NextDate datetime, 
    @Amt DECIMAL(9,2),
    @LcustId Int,
    @LStat varchar(6), 
    @LDesk varchar(4), 
    @LBkGrp int, 
    @LCyGrp int, 
    @LNextDate datetime, 
    @LAmt Decimal(9,2)


OPEN cx
FETCH NEXT FROM cx
    INTO @edate, @custId, @Stat, @Desk, @BkGrp, @CyGrp, @NextDate, @Amt

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @LCustId IS NULL 
        OR @LcustId != @CustId
    BEGIN
        SELECT 
            @LCustId = @CustID,
            @LStat = @Stat,
            @LDesk = @Desk,
            @LBkGrp = @BkGrp,
            @LCyGrp = @CyGrp,
            @LNextDate = @NextDate,
            @LAmt = @Amt

    END 
    ELSE
    BEGIN
        SELECT @Stat = ISNULL(@Stat, @LStat),
            @Desk = ISNULL(@Desk, @LDesk),
            @BkGrp = IsNull(@BkGrp, @LBkGrp),
            @CyGrp = IsNull(@CyGrp, @LCyGrp),
            @NextDate = IsNull(@NextDate, @LNextDate),
            @Amt = IsNull(@Amt, @LAmt)

        SELECT  @LStat = @Stat,
            @LDesk = @Desk,
            @LBkGrp = @BkGrp,
            @LCyGrp = @CyGrp,
            @LNextDate = @NextDate,
            @LAmt = @Amt
    END     
    INSERT INTO #tmp VALUES (@edate, @custId, @Stat, @Desk, @BkGrp, @CyGrp, @NextDate, @Amt)

    FETCH NEXT FROM cx
    INTO @edate, @custId, @Stat, @Desk, @BkGrp, @CyGrp, @NextDate, @Amt
END

Close cx
DEALLOCATE cx

SELECT *
FROM #tmp

/*
DROP TABLE #t
DROP TABLE #tmp
*/

Answer 3

Depending on the size of your data and query performance requirements, you might be able to use a recursive Common Table Expression. This example uses OPTION (MAXRECURSION 100) which would need to be increased with a larger set of data. You could compare this solution to others already provided.

(Thanks to Laughing Vergil for supplying the test data in his answer)

---

--Demo setup
set nocount on
drop table if exists #t
go
CREATE TABLE #t (EntryDate date, CustId int, Stat varchar(6), Desk varchar(4), BkGrp int, CyGrp int, NextDate date, Amt DECIMAL(9,2));
INSERT INTO #t VALUES('5/9/2018', 19001, 'PLX', 'H6', NULL, NULL, NULL, NULL);
INSERT INTO #t VALUES('5/31/2018', 19001, NULL, NULL, 0, 13, '6/17/2018', 1063.51);
INSERT INTO #t VALUES('6/4/2018', 19001, NULL, 'B98', 0, 13, '6/17/2018', 1013.51);
INSERT INTO #t VALUES('6/21/2018', 19001, 'PLY', NULL, NULL, NULL, '7/17/2018', 1033.15);
INSERT INTO #t VALUES('7/18/2018', 19001, NULL, NULL, 0, 13, '7/17/2018', 1058.15);
INSERT INTO #t VALUES('7/22/2018', 19001, NULL, NULL, 1, 13, '8/17/2018', 1077.91);
INSERT INTO #t VALUES('1/29/2016', 19503, 'PLZ', 'H6', NULL, NULL, NULL, NULL);
INSERT INTO #t VALUES('5/31/2018', 19503, NULL, NULL, 1, 5, '6/6/2018', 541.11);
INSERT INTO #t VALUES('6/7/2018', 19503, NULL, NULL, 1, NULL, '6/6/2018', 576.11);
INSERT INTO #t VALUES('6/10/2018', 19503, 'PLA', NULL, 2, 5, '7/6/2018', 589.3);
INSERT INTO #t VALUES('7/8/2018', 19503, 'PLA', NULL, NULL, 5, '7/6/2018', 624.3);
INSERT INTO #t VALUES('7/11/2018', 19503, NULL, NULL, 3, NULL, '8/6/2018', 639.84);

--The actual query
;WITH TableAndRowNumber
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            ORDER BY CustId
                ,EntryDate
            ) AS rn
    FROM #t
    )
    ,x
AS (
    --recursive cte anchor row
    SELECT EntryDate
        ,CustId
        ,Stat
        ,Desk
        ,BkGrp
        ,CyGrp
        ,NextDate
        ,Amt
        ,rn
    FROM TableAndRowNumber
    WHERE rn = 1

    UNION ALL

    SELECT y.EntryDate
        ,y.CustId
        ,CASE 
            WHEN y.CustId <> x.CustId
                THEN y.Stat
            WHEN y.Stat IS NULL
                THEN x.Stat
            ELSE y.Stat
            END AS Stat
        ,CASE 
            WHEN y.CustId <> x.CustId
                THEN y.Desk
            WHEN y.Desk IS NULL
                THEN x.Desk
            ELSE y.Desk
            END AS Desk
        ,CASE 
            WHEN y.CustId <> x.CustId
                THEN y.BkGrp
            WHEN y.BkGrp IS NULL
                THEN x.BkGrp
            ELSE y.BkGrp
            END AS BkGrp
        ,CASE 
            WHEN y.CustId <> x.CustId
                THEN y.CyGrp
            WHEN y.CyGrp IS NULL
                THEN x.CyGrp
            ELSE y.CyGrp
            END AS CyGrp
        ,CASE 
            WHEN y.CustId <> x.CustId
                THEN y.NextDate
            WHEN y.NextDate IS NULL
                THEN x.NextDate
            ELSE y.NextDate
            END AS NextDate
        ,CASE 
            WHEN y.CustId <> x.CustId
                THEN y.Amt
            WHEN y.Amt IS NULL
                THEN x.Amt
            ELSE y.Amt
            END AS Amt
        ,y.rn
    FROM x
    INNER JOIN TableAndRowNumber AS y
        ON y.rn = x.rn + 1
    )
SELECT convert(VARCHAR(10), EntryDate, 101) as EntryDate
    ,CustId
    ,Stat
    ,Desk
    ,BkGrp
    ,CyGrp
    ,convert(VARCHAR(10), NextDate, 101) as NextDate
    ,Amt
FROM x
ORDER BY CustId
    ,EntryDate
OPTION (MAXRECURSION 100);

---

| EntryDate  | CustId | Stat | Desk | BkGrp | CyGrp | NextDate   | Amt     |
|------------|--------|------|------|-------|-------|------------|---------|
| 05/09/2018 | 19001  | PLX  | H6   | NULL  | NULL  | NULL       | NULL    |
| 05/31/2018 | 19001  | PLX  | H6   | 0     | 13    | 06/17/2018 | 1063.51 |
| 06/04/2018 | 19001  | PLX  | B98  | 0     | 13    | 06/17/2018 | 1013.51 |
| 06/21/2018 | 19001  | PLY  | B98  | 0     | 13    | 07/17/2018 | 1033.15 |
| 07/18/2018 | 19001  | PLY  | B98  | 0     | 13    | 07/17/2018 | 1058.15 |
| 07/22/2018 | 19001  | PLY  | B98  | 1     | 13    | 08/17/2018 | 1077.91 |
| 01/29/2016 | 19503  | PLZ  | H6   | NULL  | NULL  | NULL       | NULL    |
| 05/31/2018 | 19503  | PLZ  | H6   | 1     | 5     | 06/06/2018 | 541.11  |
| 06/07/2018 | 19503  | PLZ  | H6   | 1     | 5     | 06/06/2018 | 576.11  |
| 06/10/2018 | 19503  | PLA  | H6   | 2     | 5     | 07/06/2018 | 589.30  |
| 07/08/2018 | 19503  | PLA  | H6   | 2     | 5     | 07/06/2018 | 624.30  |
| 07/11/2018 | 19503  | PLA  | H6   | 3     | 5     | 08/06/2018 | 639.84  |