How to fill in missing dates and values ​​in partitioned data?

Question

How to fill in missing dates and values ​​in partitioned data?

I have a lot of problems with Google Googling, since most messages seem to contain Oracle databases, and I work 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 between spaces and copy the value from the last next date. In addition, I would like to fill in the dates, so that 1) go back to the preset MINDATE (say, 12/29/2013) and 2) go to the current date (say, 01/09/2014) - and for 2) the default will be equal to 1.

So, the output will 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

-, . . , . 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;