CODEWITHSUNDEEP
sql-servert-sqldate
user781700
user781700

Reputation: 964

SQL Convert each date range into each day row

I have one simple requirement. Below is my sql table. 

ID    Cname   StartDate    EndDate      Value
1      x      01/15/2015   01/20/2015   50
2      x      01/17/2015   01/22/2015   60 
3      y      02/15/2015   02/20/2015   40
4      y      02/17/2015   02/22/2015   80

I have date range and I want to convert this each date range into each day row. Along with that whenever there is a overlap of dates it adds the value. Below is the sample output for more clarification. 

Cname   date    value
x   1/15/2015   60
x   1/16/2015   60
x   1/17/2015   110
x   1/18/2015   110
x   1/19/2015   110
x   1/20/2015   110
x   1/21/2015   60
x   1/22/2015   60
y   2/15/2015   40
y   2/16/2015   40
y   2/17/2015   120
y   2/18/2015   120
y   2/19/2015   120
y   2/20/2015   120
y   2/21/2015   80
y   2/22/2015   80

Any help would be appreciated.

Upvotes: 1

Views: 3394

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

You can use the technique described here, in order to generate a date range for each interval of your table. Then simply group by Cname and date to get the desired result set:

;WITH natural AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val 
  FROM sys.all_objects
) 
SELECT m.Cname, d = DATEADD(DAY, natural.val, m.StartDate), 
       SUM(value) AS  value
FROM mytable AS m 
INNER JOIN natural ON natural.val <= DATEDIFF(DAY, m.StartDate, m.EndDate)
GROUP BY Cname, DATEADD(DAY, natural.val, m.StartDate)
ORDER BY Cname, d

The CTE is used to create a tally table. The numbers of this table are then used to add 1,2,3, ... days to StartDate until EndDate is reached.

If you group by Cname, [Date], then SUM will return the required value since it will add any overlapping records within each Cname partition.

SQL Fiddle Demo

Upvotes: 4

Related Questions