SQL Generate daily records from date range

Question

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

Could you please provide some more information on your date table that you would be joining? — FutbolFan
– FutbolFan, Commented Apr 30, 2015 at 14:07
tried on joining the date table, but I'm stuck on how to get the updateDate from the privious record — NCS
– NCS, Commented Apr 30, 2015 at 14:12

score 1 · Accepted Answer · 2015-04-30 14:25:12Z

1

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.

edited Apr 30, 2015 at 14:25

answered Apr 30, 2015 at 14:14

user359040

Sign up to request clarification or add additional context in comments.

5 Comments

Zohar Peled Over a year ago

I've played with this a little to make it more dynamic. check it out.

NCS Over a year ago

@ZoharPeled, Do you have tips to change this to make it work with more id's?

Zohar Peled Over a year ago

@NCS, Not at the moment, sorry. I've tried but without success. perhaps Mark will find the way

user359040 Over a year ago

@NCS: Do you have a separate table holding the ids that you want to query, and if so, do you want them all to have the same date limits (ie. 1-6 Jan 2015, as above)? Alternatively, do you just want to pick all of the ids in the Source table, and use the minimum startDate and maximum updateDate for each id as the limits?

NCS Over a year ago

@MarkBannister: Ok, I'll try that. Thanks a lot for all the effort guys, really appreciated!

sarin · Accepted Answer · 2015-04-30 14:31:28Z

0

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

answered Apr 30, 2015 at 14:31

sarin

5,3173 gold badges39 silver badges65 bronze badges

Collectives™ on Stack Overflow

SQL Generate daily records from date range

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related