CODEWITHSUNDEEP
sqlsql-server-2008
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

Cross apply to fill down values with multiple columns

I have a table with a few columns. I want to fill down values to replace nulls, but this is complicated by the additional columns. Here is a sample of what I have:

date    id1   id2   id3   id4    value
1/1/14   a     1     1     1       1.2
1/2/14   a     1     1     1       NULL
1/8/14   a     1     1     1       2.3
1/1/14   a     2     1     1       10.1
1/2/14   a     2     1     1       12.3
1/17/14  a     2     1     1       NULL
1/18/14  a     2     1     1       10.8
1/1/14   a     2     3     1       100.3
1/2/14   a     2     3     1       NULL
1/6/14   a     2     3     1       110.4

I want to copy down value while the value remains within a "group" of id1-4. For example, all of the "A-1-1-1" should be isolated from "a-2-1-1" in terms of what values to copy down. The output I need is:

date    id1   id2   id3   id4    value
1/1/14   a     1     1     1       1.2
1/2/14   a     1     1     1       1.2
1/8/14   a     1     1     1       2.3
1/1/14   a     2     1     1       10.1
1/2/14   a     2     1     1       12.3
1/17/14  a     2     1     1       12.3
1/18/14  a     2     1     1       10.8
1/1/14   a     2     3     1       100.3
1/2/14   a     2     3     1       100.3
1/6/14   a     2     3     1       110.4

I can do this for a single column using CROSS APPLY but the syntax for the multiple columns is confusing me. The SQL to generate the temp data is:

DECLARE @test TABLE
    (
    date DATETIME
    ,id1 VARCHAR(1)
    ,id2 INT
    ,id3 INT
    ,id4 INT
    ,value FLOAT
    )

    INSERT INTO @test VALUES
    ('2014-01-01','a','1','1','1','1.2')
    ,('2014-01-02','a','1','1','1',NULL)
    ,('2014-01-08','a','1','1','1','2.3')
    ,('2014-01-01','a','2','1','1','10.1')
    ,('2014-01-02','a','2','1','1','12.3')
    ,('2014-01-17','a','2','1','1',NULL)
    ,('2014-01-18','a','2','1','1','10.8')
    ,('2014-01-01','a','2','3','1','100.3')
    ,('2014-01-02','a','2','3','1',NULL)
    ,('2014-01-06','a','2','3','1','110.4')
    ;

    SELECT * FROM @test;

Upvotes: 1

Views: 1796

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use apply for this:

select t.*, coalesce(t.value, tprev.value) as value
from @test t outer apply
     (select top 1 value
      from @test t2
      where t2.id1 = t.id1 and t2.id2 = t.id2 and t2.id3 = t.id3 and t2.id4 = t.id4 and
            t2.date < t.date and t2.value is not null
      order by t2.date desc
     ) tprev;

Upvotes: 4

Related Questions