Calculate Difference From Previous Row with NULLs (Fill Null Gap)


You will find two options to write queries to get the result. The Outer Apply method looks very compact but it is not performing well. Instead the LEFT JOIN way is a lot quicker.


Create Table MyTable (ID int, RecordDtTm Datetime, TotalScore int, DeltaScore int)
--Sample data
insert into MyTable (ID, RecordDtTm, TotalScore, DeltaScore)
Values 
(2, '2012-01-01 15:15', 15, NULL),
(2, '2012-01-01 15:25', NULL, NULL),
(2, '2012-01-01 15:55', NULL, NULL),
(2, '2012-01-01 16:10', 12, NULL),
(2, '2012-01-01 16:30', 12, NULL),
(2, '2012-01-01 16:45', 9, NULL),
(2, '2012-01-01 16:55', NULL, NULL),
(2, '2012-01-01 17:12', 10, NULL)

--Expected result
/*
ID RecordDtTm TotalScore DeltaScore
2 2012-01-01 15:15 15 NULL
2 2012-01-01 15:25 NULL 0
2 2012-01-01 15:55 NULL 0
2 2012-01-01 16:10 12 -3
2 2012-01-01 16:30 12 0
2 2012-01-01 16:45 09 -3
2 2012-01-01 16:55 NULL 0
2 2012-01-01 17:12 10 1
*/

--Solution 1
;With mycte as
(select *, row_number() over(order by RecordDtTm asc) rn from MyTable where TotalScore is not null)

Select m.Id, m.RecordDtTm, m.TotalScore, (m1.TotalScore-m2.TotalScore) as DeltaScore
From MyTable m
Left join mycte m1 on m.id=m1.id And m.RecordDtTm=m1.RecordDtTm
Left join mycte m2 on m1.rn=m2.rn+1
Order by m.Id, m.RecordDtTm

--Solution2 Noamiā€™s
select T.Id, T.RecordDtTm, T.TotalScore,
case when T.TotalScore IS NULL and T1.TotalScore IS NULL then 0 else T.TotalScore - T1.TotalScore END as DeltaScore
FROM myTable T
OUTER APPLY (select top (1) TotalScore from myTable T1 where T1.Id = T.Id
and T1.RecordDtTm < T.RecordDtTm and T1.TotalScore IS NOT NULL
ORDER BY RecordDtTm DESC) T1


drop table myTable
--Thread at MSDN:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4b8efff4-bb98-43da-92fb-13c05d83bc61

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s