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
Advertisement
Answer
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;