sql - How to fill missing dates and values in partitioned data?

Question

How to fill missing dates and values in partitioned data?

I'm having a lot of trouble Googling this as most of the posts seem to feature Oracle databases, and I'm working with Microsoft SQL Server.

I have the following hypothetical table:

name       date          val
-------------------------------
A          01/01/2014     1.5
A          01/03/2014     2
A          01/06/2014     5
B          01/02/2014     90
B          01/07/2014     10

I want to fill in the dates in between the gaps and copy over the value from the most recent following date. In addition, I would like to fill in dates that 1) go back to a pre-set MINDATE (let's say it's 12/29/2013) and 2) go up to the current date (let's say it's 01/09/2014) - and for 2) the default values will be 1.

So, the output would be:

name       date          val
-------------------------------
A          12/29/2014     1.5
A          12/30/2014     1.5
A          12/31/2014     1.5
A          01/01/2014     1.5   <- original
A          01/02/2014     2
A          01/03/2014     2     <- original
A          01/04/2014     5
A          01/05/2014     5
A          01/06/2014     5     <- original
A          01/07/2014     1
A          01/08/2014     1
A          01/09/2014     1
B          12/29/2014     90
B          12/30/2014     90
B          12/31/2014     90
B          01/01/2014     90
B          01/02/2014     90    <- original
B          01/03/2014     10
B          01/04/2014     10
B          01/05/2014     10
B          01/06/2014     10
B          01/07/2014     10    <- original
B          01/08/2014     1
B          01/09/2014     1

Answer 1

First, you need to generate the dates. Then you can generate all the combinations of date and name. Finally, fill in the values. Here is an example using cross apply:

with dates as (
      select @MINDATE as thedate
      union all
      select dateadd(day, 1, thedate)
      from dates
      where dateadd(day, 1, thedate) <= getdate()
     )
select thedate, vals.val
from dates cross join
     (select distinct name from hypothetical) h cross apply
     (select top 1 val
      from hypothetical h2
      where h2.name = h.name and h2.date <= dates.thedate
      order by date desc
     ) vals;