1

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.

0

3 Answers 3

3

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.

0
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
*/
1

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  |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.