Geek Answers Handbook

Reference and Cascading Zero Columns

I have a table that contains values ​​for specific months:

  |  MFG |  DATE |  FACTOR |
 -----------------------------
 |  1 |  2013-01-01 |  1 |
 |  2 |  2013-01-01 |  0.8 |
 |  2 |  2013-02-01 |  1 |
 |  2 |  2013-12-01 |  1.55 |
 |  3 |  2013-01-01 |  1 |
 |  3 |  2013-04-01 |  1.3 |
 |  3 |  2013-05-01 |  1.2 |
 |  3 |  2013-06-01 |  1.1 |
 |  3 |  2013-07-01 |  1 |
 |  4 |  2013-01-01 |  0.9 |
 |  4 |  2013-02-01 |  1 |
 |  4 |  2013-12-01 |  1.8 |
 |  5 |  2013-01-01 |  1.4 |
 |  5 |  2013-02-01 |  1 |
 |  5 |  2013-10-01 |  1.3 |
 |  5 |  2013-11-01 |  1.2 |
 |  5 |  2013-12-01 |  1.5 |

What I would like to do is expand them using the calendar table (already defined):

Y29si.png

Finally, cascade NULL columns to use the previous value.

MuOvF.png

So far I have received a request that fills NULL last value for mfg = 3 . Each mfg will always matter for the first year. My question is: how can I deploy this and go to all mfg ?

 SELECT c.[date], f.[factor], Isnull(f.[factor], (SELECT TOP 1 factor FROM factors WHERE [date] < c.[date] AND [factor] IS NOT NULL AND mfg = 3 ORDER BY [date] DESC)) AS xFactor FROM (SELECT [date] FROM calendar WHERE Datepart(yy, [date]) = 2013 AND Datepart(d, [date]) = 1) c LEFT JOIN (SELECT [date], [factor] FROM factors WHERE mfg = 3) f ON f.[date] = c.[date]

Result

  |  DATE |  FACTOR |  XFACTOR |
 ---------------------------------
 |  2013-01-01 |  1 |  1 |
 |  2013-02-01 |  (null) |  1 |
 |  2013-03-01 |  (null) |  1 |
 |  2013-04-01 |  1.3 |  1.3 |
 |  2013-05-01 |  1.2 |  1.2 |
 |  2013-06-01 |  1.1 |  1.1 |
 |  2013-07-01 |  1 |  1 |
 |  2013-08-01 |  (null) |  1 |
 |  2013-09-01 |  (null) |  1 |
 |  2013-10-01 |  (null) |  1 |
 |  2013-11-01 |  (null) |  1 |
 |  2013-12-01 |  (null) |  1 |

SQL Fiddle

+6
source share
1 answer

I don't know if dates are needed for dynamic ones from the calendar table or if mfg can be more than 5, but this should give you some ideas.

 select * from ( select c.date, t.mfg, ( select top 1 f.factor from factors as f where f.date <= c.date and f.mfg = t.mfg and f.factor is not null order by f.date desc ) as factor from calendar as c cross apply(values(1),(2),(3),(4),(5)) as t(mfg) ) as t pivot ( max(t.factor) for t.date in ([20130101], [20130201], [20130301], [20130401], [20130501], [20130601], [20130701], [20130801], [20130901], [20131001], [20131101], [20131201]) ) as P

SQL Fiddle

+6
source

More articles:


All Articles