How To Fill Missing Dates And Values In Partitioned Data?

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 Micr

Solution 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;