Filling in missing data

Question

I am working in SQL Server. Suppose I have the following table:

3/1/2013    aaa   7     5
1/1/2013    bbb   1     2
10/10/2012  ccc   5     8
9/9/2012    ddd   6     9
8/8/2012    bbb   2     3

I would like to take the values from the "bbb" rows and propagate them upwards (ordering by date) essentially making:

3/1/2013    aaa   1     2
1/1/2013    bbb   1     2
10/10/2012  ccc   2     3
9/9/2012    ddd   2     3
8/8/2012    bbb   2     3

What is the best way to do this in SQL Server?

Do you want to update or just select? – Nenad Zivkovic Aug 07 '13 at 19:27 — Nenad Zivkovic, Aug 07 '13 at 19:27

xanatos · Accepted Answer · 2013-08-07T19:44:46.840

2

Something pretty basic could be

SELECT MT.Date, MT.Text, 
       CASE WHEN MT.Text = 'bbb' THEN Number
            ELSE (SELECT TOP 1 Number 
                               FROM MyTable MT2 
                               WHERE MT2.Date < MT.Date AND 
                                     MT2.Text = 'bbb'
                               ORDER BY MT2.Date DESC)
            END Number,
       CASE WHEN MT.Text = 'bbb' THEN Number2
            ELSE (SELECT TOP 1 Number2 
                               FROM MyTable MT2 
                               WHERE MT2.Date < MT.Date AND 
                                     MT2.Text = 'bbb'
                               ORDER BY MT2.Date DESC)
            END Number2 
       FROM MyTable MT

SQLFiddle: http://sqlfiddle.com/#!3/cbee5/3

or using OUTER APPLY (it should be faster)

SELECT MT.Date, MT.Text, 
       CASE WHEN MT.Text = 'bbb' THEN MT.Number
            ELSE MT2.Number 
            END Number,
       CASE WHEN MT.Text = 'bbb' THEN MT.Number2
            ELSE MT2.Number2
            END Number2
       FROM MyTable MT
       OUTER APPLY (SELECT TOP 1 MT2.Number, MT2.Number2 
                                 FROM MyTable MT2
                                 WHERE MT.Text <> 'bbb' AND 
                                       MT2.Text = 'bbb' AND 
                                       MT2.Date < MT.Date
                                 ORDER BY MT2.Date DESC
                   ) MT2

SQLFiddle: http://sqlfiddle.com/#!3/cbee5/7

edited Aug 07 '13 at 19:44

answered Aug 07 '13 at 19:18

xanatos

109,618
12
197
280

Is this possible with a LEFT JOIN? – Denis Aug 07 '13 at 19:26
@Denis I think you would need a `CROSS APPLY` – xanatos Aug 07 '13 at 19:31
@Denis But what you are looking for is probably described here: http://stackoverflow.com/questions/7045040/replace-null-values-with-latest-non-null-value-in-resultset-series-sql-server-2 – xanatos Aug 07 '13 at 19:33
I updated my question to show what I meant. I guess I could always just have 2 columns generated the way you suggested but doesn't sound efficient. – Denis Aug 07 '13 at 19:33
@Denis Changed the SELECTs, now with *more* columns :-) – xanatos Aug 07 '13 at 19:46
this worked great! Thank you - I was thinking about this with partitioning but your way is much easier... – Denis Aug 07 '13 at 20:04
@Denis `CROSS APPLY` / `OUTER APPLY` are like `INNER` / `LEFT` joins where the joined "object" in some way depends for some "parameters" on the other tables. Here for example we have to compare with the `MT.Date`. Other "standard" uses are with table functions that require parameters. – xanatos Aug 07 '13 at 20:05

score 1 · Answer 2 · answered Aug 07 '13 at 20:08

To select, you can go as simple as;

SELECT t1.date, t1.tag, t2.val, t2.val2
FROM Table1 t1
JOIN Table1 t2 
  ON t2.tag='bbb' AND t1.date >= t2.date
LEFT JOIN Table1 t3 ON t3.tag = 'bbb' AND t1.date >= t3.date AND t3.date > t2.date
WHERE t3.date IS NULL

An SQLfiddle to test with.

Updating the content of the table, you can use pretty much the same query;

UPDATE t1
SET val=t2.val, val2=t2.val2
FROM Table1 t1
JOIN Table1 t2 
  ON t2.tag='bbb' AND t1.date >= t2.date
LEFT JOIN Table1 t3 ON t3.tag = 'bbb' AND t1.date >= t3.date AND t3.date > t2.date
WHERE t3.date IS NULL

Another SQLfiddle.

Filling in missing data

2 Answers2