Filling gaps, carrying forward data using SQL Windowing functions

Question

I am trying to solve the problem of filling gaps in data, pulling forward previous data (if it exists. I can solve it using the "quirky update" method but am trying to leverage the new SQL windowing functions available in SQL 2012. I also don't want cursors or triangular joins or recursive CTE's.

The data has DayID (date), AssetID and RunningQuantity. I don't want to carry forward until the first non-null RunningQuantity for an AssetID, then I want to carry forward the AssetID and RunningQuantity until RunningQuantity changes, then carry the new RunningQuantity forward. If the AssetID changes or the dayID sequence is broken ie. order by dayid and dayid is not next in sequence, reset for new AssetID and repeat steps.

Here is the sql with starting data, my failed attempt and then the desired results:


if OBJECT_ID('tempdb..#test') is not null
    drop table #test
create table #test (
    DayID_in int,
    AssetID_in int,
    RunningQuantity_fl float )

insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77420,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77421,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77422,317728,2841.716)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77423,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77424,317728,2641.677)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77425,317728,1688.731)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77426,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77427,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77420,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77421,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77422,317744,1527.03)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77423,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77424,317744,1544.748)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77425,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77426,317744,1544.748)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77427,317744,1706.072)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77428,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77429,317745,7324.573)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77430,NULL,NULL)
insert into #test (DayID_in,AssetID_in,RunningQuantity_fl) values (77431,NULL,NULL)

select b.DayID_in,b.AssetID_in,b.RunningQuantity_fl,PreviousQTY=lag(b.runningQuantity_fl)
     over (Partition by b.assetID_in order by b.AssetID_in,b.dayID_in)
from #test b

if OBJECT_ID('tempdb..#desiredResult') is not null
    drop table #desiredResult
create table #desiredResult (
    DayID_in int,
    AssetID_in int,
    RunningQuantity_fl float )

insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77420,NULL,NULL)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77421,NULL,NULL)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77422,317728,2841.716)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77423,317728,2841.716)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77424,317728,2641.677)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77425,317728,1688.731)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77426,317728,1688.731)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77427,317728,1688.731)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77420,NULL,NULL)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77421,NULL,NULL)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77422,317744,1527.03)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77423,317744,1527.03)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77424,317744,1544.748)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77425,317744,1544.748)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77426,317744,1544.748)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77427,317744,1706.072)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77428,317744,1706.072)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77429,317745,7324.573)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77430,317744,7324.573)
insert into #desiredResult (DayID_in,AssetID_in,RunningQuantity_fl) values (77431,317744,7324.573)

select * from #desiredResult

score 0 · Answer 1 · edited May 23 '17 at 10:31

The "standard" SQL way to do this is:

select *,
       (select top 1 assetid_in
        from test t2
        where t2.dayid_in <= t.dayid_in and
              t2.assetid_in is not null
        order by t2.dayid_in desc
       ) assetid_in2,
       (select top 1 RUNNINGQUANTITY_FL
        from test t2
        where t2.dayid_in <= t.dayid_in and
              t2.RUNNINGQUANTITY_FL is not null
        order by t2.dayid_in desc
       ) running2
from test t;

Here is a SQL Fiddle.

SQL Server offers the ability to do a cross apply for the same thing. You can do this with one cross apply call if the NULL values for the two columns are in the same row. Here is an example.

The more advanced window functions don't particularly help (you can do what you want with multiple functions for each column). Oracle has an option on lag() called ignore nulls. This does exactly what you want.

Filling gaps, carrying forward data using SQL Windowing functions

1 Answers1