Get missing records with missing date and update value with previous Non Null value

Question

I have following Table where teachers update the data daily but some teachers don't update the record daily. I need to find missing date and add previous NOT NULL value in Val and TotalValue field. I have date Table and I did left join with Date table but unable to update not null records. 2nd January and 3rd January are missing rows, I need to add rows to the result

DECLARE @Table TABLE(
        ID INT,
        ClassID VARCHAR(10),
        Val INT,
        TotalValue INT,
        UpdatedDateTime DATETIME2

)

INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'AB475', 20, 100, '2020/1/1 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'ZXCV5', 50, 70, '2020/1/1 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'GHT09', 40, 40, '2020/1/1 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'AB475', 70, 100, '2020/1/2 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'GHT09', 38, 40, '2020/1/2 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'AB475', 20, 100, '2020/1/3 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'ZXCV5', 20, 70, '2020/1/3 14:00:20'

SELECT * FROM @Table

ID  ClassID Val TotalValue  UpdatedDateTime
1   AB475   20  100 2020-01-01 14:00:20.0000000
1   ZXCV5   50  70  2020-01-01 14:00:20.0000000
1   GHT09   40  40  2020-01-01 14:00:20.0000000
1   AB475   70  100 2020-01-02 14:00:20.0000000
1   GHT09   38  40  2020-01-02 14:00:20.0000000
1   AB475   20  100 2020-01-03 14:00:20.0000000
1   ZXCV5   20  70  2020-01-03 14:00:20.0000000

SELECT *
FROM [DATE]
LEFT JOIN @Table
    ON [DATE].[DATE] = CAST([@Table].UpdatedDateTime AS DATE)

End Result should be

ID  ClassID Val TotalValue  UpdatedDateTime
1   AB475   20  100 2020-01-01 14:00:20.0000000
1   ZXCV5   50  70  2020-01-01 14:00:20.0000000
1   GHT09   40  40  2020-01-01 14:00:20.0000000
1   AB475   70  100 2020-01-02 14:00:20.0000000
1   GHT09   38  40  2020-01-02 14:00:20.0000000
1   AB475   20  100 2020-01-03 14:00:20.0000000
1   ZXCV5   20  70  2020-01-03 14:00:20.0000000
1   ZXCV5   50  70  2020-01-02 14:00:20.0000000
1   GHT09   38  40  2020-01-03 14:00:20.0000000

I don't understand the results. Why is ZXCVS 20/70 on 2020-01-03? — Gordon Linoff
– Gordon Linoff, Commented Apr 2, 2020 at 18:34
It is updated by teacher, ZXCVS was updated on 1st and 3rd January, it was missing the data for 2nd January so, I need the data for 2nd January with val and TotalValue column from 1st January since, it wasn't updated. — Justin
– Justin, Commented Apr 2, 2020 at 18:43

Suraj Kumar · Accepted Answer · 2020-04-02 18:30:49Z

I would cross join two select distinct queries to generate all possible combinations of teachers and dates, and then use cross apply to get the missing information:

select c.ID, c.ClassID, t.Val, t.TotalValue, d.UpdatedDateTime
from (select distinct ID, ClassID from @table) c
cross join (select distinct UpdatedDateTime from @table) d
cross apply (
    select top 1 *
    from @table t
    where t.ClassID = c.ClassID and t.UpdatedDateTime <= d.UpdatedDateTime
    order by t.UpdatedDateTime desc
) t
order by c.ID, c.classId, d.UpdatedDateTime

Demo on DB Fiddle:

ID | ClassID | Val | TotalValue | UpdatedDateTime            
-: | :------ | --: | ---------: | :--------------------------
 1 | AB475   |  20 |        100 | 2020-01-01 14:00:20.0000000
 1 | AB475   |  70 |        100 | 2020-01-02 14:00:20.0000000
 1 | AB475   |  20 |        100 | 2020-01-03 14:00:20.0000000
 1 | GHT09   |  40 |         40 | 2020-01-01 14:00:20.0000000
 1 | GHT09   |  38 |         40 | 2020-01-02 14:00:20.0000000
 1 | GHT09   |  38 |         40 | 2020-01-03 14:00:20.0000000
 1 | ZXCV5   |  50 |         70 | 2020-01-01 14:00:20.0000000
 1 | ZXCV5   |  50 |         70 | 2020-01-02 14:00:20.0000000
 1 | ZXCV5   |  20 |         70 | 2020-01-03 14:00:20.0000000

Collectives™ on Stack Overflow

Get missing records with missing date and update value with previous Non Null value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related