CODEWITHSUNDEEP
sqlsql-servert-sql
NCS
NCS

Reputation: 193

SQL Generate daily records from date range

I've got the next situation (SQL SERVER 2012):

This is my source table (dates are dd-mm-yyyy):

id|startDate   |updateDate  |percentage
--|------------|------------|----------
01|  01-01-2015|  03-01-2015|  45
01|  01-01-2015|  05-01-2015|  80
01|  01-01-2015|  06-01-2015|  100

I want to end up with the folowing table :

id|date        |percentage
--|------------|----------
01|  01-01-2015|  0
01|  02-01-2015|  0
01|  03-01-2015|  45
01|  04-01-2015|  45
01|  05-01-2015|  80
01|  06-01-2015|  100

My plan is to do a join with a date table, but my main concern is how to generate the records where the percentage is 0, since the source only has a startdate and a first updatedate.

So as I see it, first I need to do a datediff between startDate and first updateDate, en from there a datediff between current updateDate and the previous updateDate. Thats the bit where my concern is, how can I make this work?

thanks in advance!

PS. datetable is just a simple table with every date

id|date        |month |etc...
--|------------|------|-------
01|  01-01-2015|  1   |
01|  02-01-2015|  1   |
01|  03-01-2015|  1   |
01|  04-01-2015|  1   |
01|  05-01-2015|  1   |
01|  06-01-2015|  1   |
etc...

Upvotes: 0

Views: 1418

Answers (2)

sarin
sarin

Reputation: 5307

First join your date helper table with your source table on updateDate. I have then correlated a sub-query to get the maximum percentage for any dates less than the date we are currently joining on.

With tblDates AS (
        select *
        from Dates d
        left join Table1 t on CAST(d.date as date) = t.updateDate
        )
    select  d.id, 
            d.[date],
            (select ISNULL(max(percentage), 0)
            from tblDates d1
            where d1.[date] < d.[date]) as percentage
    from tblDates d

Upvotes: 0

user359040
user359040

Reputation:

One approach (using a recursive CTE):

; with cte as
(select '01' id, cast('2015-01-01' as datetime) [date], 0 percentage
 union all
 select c.id, 
        dateadd(DAY,1,c.[date]) [date], 
        coalesce((select s.percentage
                  from SourceTable s 
                  where c.id = s.id and dateadd(DAY,1,c.[date]) = s.updatedate), 
                 c.percentage) percentage
 from cte c
 where c.[date] < '2015-01-06')
select * from cte

SQLFiddle here.

Upvotes: 1

Related Questions