SQL breakout date range to rows

Question

I am trying to take given date ranges found in a data set and divide them into unique rows for each day in the range (example below). Doing the opposite in SQL is pretty straight forward, but I am struggling to achieve the desired query output.

Beginning data:

ITEM    START_DATE  END_DATE
A       1/1/2015    1/5/2015
B       2/5/2015    2/7/2015

Desired query output:

 ITEM   DATE_COVERED
 A      1/1/2015
 A      1/2/2015
 A      1/3/2015
 A      1/4/2015
 A      1/5/2015
 B      2/5/2015
 B      2/6/2015
 B      2/7/2015

Looks like an interesting problem. What queries have you tried yourself? — Radu Gheorghiu, Jun 10 '15 at 06:28
possible duplicate of [SQL how to convert row with date range to many rows with each date](http://stackoverflow.com/questions/16358959/sql-how-to-convert-row-with-date-range-to-many-rows-with-each-date) — Jeremy C., Jun 10 '15 at 06:36

score 2 · Answer 1 · answered Jun 10 '15 at 06:32

The fastest way will be some tally table:

DECLARE @t TABLE
    (
      ITEM CHAR(1) ,
      START_DATE DATE ,
      END_DATE DATE
    )
INSERT  INTO @t
VALUES  ( 'A', '1/1/2015', '1/5/2015' ),
        ( 'B', '2/5/2015', '2/7/2015' )



;WITH cte AS(SELECT -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) d FROM
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t3(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t4(n))

SELECT t.ITEM, ca.DATE_COVERED FROM @t t
CROSS APPLY(SELECT DATEADD(dd, d, t.START_DATE) AS DATE_COVERED 
            FROM cte 
            WHERE DATEADD(dd, d, t.START_DATE) BETWEEN t.START_DATE AND t.END_DATE) ca   
ORDER BY t.ITEM, ca.DATE_COVERED

How to predict cross join add in a with CTE, when the date difference very long.like ( 'A', '1/1/1965', '1/5/2015' ) — Vinit Patel, Jun 10 '15 at 06:58
@VinitPatel, just by adding more cross joins to sure it covers all ranges, but I doubt that he will need more then 5 cross join because 5 joins will cover 275 years. 6 joins 2732 years. — Giorgi Nakeuri, Jun 10 '15 at 07:06

score 1 · Answer 2 · answered Jun 10 '15 at 06:35

Query:

SQLFiddleExample

SELECT t.ITEM, 
        DATEADD(day,n.number, t.START_DATE) AS DATE_COVERED
FROM Table1 t,
(SELECT number
FROM master..spt_values
WHERE [type] = 'P') n
WHERE START_DATE <= DATEADD(day,n.number, t.START_DATE)
AND END_DATE >= DATEADD(day,n.number, t.START_DATE)

Result:

| ITEM | DATE_COVERED |
|------|--------------|
|    A |   2015-01-01 |
|    A |   2015-01-02 |
|    A |   2015-01-03 |
|    A |   2015-01-04 |
|    A |   2015-01-05 |
|    B |   2015-02-05 |
|    B |   2015-02-06 |
|    B |   2015-02-07 |

score 1 · Answer 3 · edited May 23 '17 at 12:14

1

NOTE: this only works if the difference between your startdate and enddate is a maximum of 2047 days (master..spt_values only allows 0..2047 range of values)

 select item, dateadd(d,v.number,d.start_date) adate
 from begindata d
 join master..spt_values v on v.type='P'
           and v.number between 0 and datediff(d, start_date, end_date)
 order by adate;

I'd like to say I did this myself but I got the code from this

Here is a fiddle with your expected result

edited May 23 '17 at 12:14

Community

1
1

answered Jun 10 '15 at 06:35

Jeremy C.

2,405
1
12
28

In your Code there is limitation, recoreds up to 2048 no more recoreds are there.... – Vinit Patel Jun 10 '15 at 06:56
I just checked on my fiddle and got a resultset of 2053 and after that I did one with 4096 results by changing both start dates to year 2000 so I don't really know what you mean @VinitPatel – Jeremy C. Jun 10 '15 at 07:03
ITEM START_DATE END_DATE A 1/1/1985 1/5/2015 try this one and tell me @Jeremy C. – Vinit Patel Jun 10 '15 at 07:08
@VinitPatel oh I see you mean date range of max 2048, let me see if I can work around that – Jeremy C. Jun 10 '15 at 07:10
I forgot about the 0..2047 range on master..spt_values – Jeremy C. Jun 10 '15 at 07:21
oh well seeing as the others work I'll just edit my answer with the note that it only works for 2048 days of diff instead of looking for a solution, I have other work to do :D – Jeremy C. Jun 10 '15 at 07:27

score 0 · Answer 4 · answered Jun 10 '15 at 07:14

TRY THIS...

CREATE TABLE Table1
    ([ITEM] varchar(1), [START_DATE] date, [END_DATE] date)
;

INSERT INTO Table1
    ([ITEM], [START_DATE], [END_DATE])
VALUES    ('A', '2015-01-01', '2015-01-05'), ('B', '2015-02-05', 2015-02-07');

WITH    Days
          AS ( SELECT ITEM,  START_DATE AS [Date], 1 AS [level] from Table1
               UNION ALL
               SELECT  TABLE1.ITEM, DATEADD(DAY, 1, [Date]), [level] + 1
               FROM     Days,Table1
               WHERE  DAYS.ITEM=TABLE1.ITEM AND  [Date] < END_DATE )
     SELECT distinct [Date]
     FROM   Days

DEMO

SQL breakout date range to rows

4 Answers4