SQL how to convert row with date range to many rows with each date

Question

If I have a table that looks like this

begin date      end date        data
 2013-01-01     2013-01-04       7
 2013-01-05     2013-01-06       9

How can I make it be returned like this...

    date         data
 2013-01-01       7
 2013-01-02       7
 2013-01-03       7
 2013-01-04       7
 2013-01-05       9
 2013-01-06       9

One thing I was thinking of doing is to have another table that just has all the dates and then join the table with just dates to the above table using date>=begin date and date<=end date but that seems a little clunky to have to maintain that extra table with nothing but repetitive dates.

In some instances I don't have a data range but just an as of date which basically looks like my first example but with no end date. The end date is implied by the next row's 'as of' date (ie end date should be the next row's as of -1). I had a "solution" for this that uses the row_number() function to get the next value but I suspect that methodology, which the way I'm doing it has a bunch of nested self joins, contributes to very long query times.

You're reinterpreting data, for which you shouldn't use SQL. On the business logic end this is 2~3 lines of code extra without burdening the database with something it was never meant to be able to do easily. — Niels Keurentjes, May 03 '13 at 12:32
@Niels Which part are you saying shouldn't happen on the SQL server? Are you saying I should store my data in the format that I want it to be displayed or that I should use another process besides the SQL server to generate the output in the format that I want? — Dean MacGregor, May 03 '13 at 18:29
No, you should store data in the DB in a normalized correct indexable fashion, as you are doing now. Presentation is not a DB's task, so do that in the invoking code. — Niels Keurentjes, May 03 '13 at 19:22

RichardTheKiwi · Accepted Answer · 2019-07-30T19:34:14.137

18

Using some sample data...

create table data (begindate datetime, enddate datetime, data int);
insert data select 
 '20130101', '20130104', 7 union all select
 '20130105', '20130106', 9;

The Query: (Note: if you already have a numbers/tally table - use it)

select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join master..spt_values v on v.type='P'
       and v.number between 0 and datediff(d, begindate, enddate)
order by adate;

Results:

|                       COLUMN_0 | DATA |
-----------------------------------------
| January, 01 2013 00:00:00+0000 |    7 |
| January, 02 2013 00:00:00+0000 |    7 |
| January, 03 2013 00:00:00+0000 |    7 |
| January, 04 2013 00:00:00+0000 |    7 |
| January, 05 2013 00:00:00+0000 |    9 |
| January, 06 2013 00:00:00+0000 |    9 |

Alternatively you can generate a number table on the fly (0-99) or as many numbers as you need

;WITH Numbers(number) AS (
  select top(100) row_number() over (order by (select 0))-1
  from sys.columns a
  cross join sys.columns b
  cross join sys.columns c
  cross join sys.columns d
  )
select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join Numbers v on v.number between 0 and datediff(d, begindate, enddate)
order by adate;

SQL Fiddle Demo

edited Jul 30 '19 at 19:34

answered May 03 '13 at 12:32

RichardTheKiwi

105,798
26
196
262

Despite admittedly being a rather brilliant solution to the problem, the `spt_values` table is an undocumented feature of SQL Server, and as such not guaranted to keep working, or never change. Ref [this topic](http://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-me) – Niels Keurentjes May 03 '13 at 12:36
I don't believe the table will ever go away, but I have added an alternative. Thanks for the feedback – RichardTheKiwi May 03 '13 at 12:45
So glad I found this question & answer. This was part of a test in a job interview. The goal made sense to me, but the solution is tricky. Glad I found your simple and perfect solution! – SherlockSpreadsheets Mar 29 '19 at 14:04
The fiddle is failing, does this still work in 2019? – Miguel Stevens Jul 29 '19 at 13:15
@Notflip sqlfiddle had no working sql2014 hosts, switched link to use sql2017 – RichardTheKiwi Jul 30 '19 at 19:35

score 3 · Answer 2 · answered May 03 '13 at 13:01

You can use recursive CTE to get all the dates between two dates. Another CTE is to get ROW_NUMBERs to help you with those missing EndDates.

DECLARE @startDate DATE
DECLARE @endDate DATE

SELECT @startDate = MIN(begindate) FROM Table1
SELECT @endDate = MAX(enddate) FROM Table1

;WITH CTE_Dates AS 
(
    SELECT @startDate AS DT
    UNION ALL
    SELECT DATEADD(DD, 1, DT)
    FROM CTE_Dates
    WHERE DATEADD(DD, 1, DT) <= @endDate
)
,CTE_Data AS 
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY BeginDate) AS RN FROM Table1

)
SELECT DT, t1.data FROM CTE_Dates d
LEFT JOIN CTE_Data t1 on d.DT 
BETWEEN t1.[BeginDate] AND COALESCE(t1.EndDate, 
        (SELECT DATEADD(DD,-1,t2.BeginDate) FROM CTE_Data t2 WHERE t1.RN + 1 = t2.RN))

SQLFiddle DEMO

score 0 · Answer 3 · answered Mar 31 '23 at 20:10

DECLARE @tblDateRange TABLE (
    Id INT,
    FromDateTime DATETIME,
    ToDateTime DATETIME
)
INSERT INTO @tblDateRange (Id, FromDateTime, ToDateTime)
VALUES
(1, '2023-01-01 10:00:00.000', '2023-01-01 10:00:00.000'),      --starts and ends in same
(2, '2023-02-02 00:00:00.000', '2023-02-04 23:59:59.000'),      --starts and ends in standered
(3, '2023-03-05 10:00:00.000', '2023-03-06 23:59:59.000'),      --starts in middle of another date
(4, '2023-04-07 00:00:00.000', '2023-04-08 21:00:00.000'),      --ends in middle of another date
(5, '2023-05-09 11:00:00.000', '2023-05-11 11:00:00.000'),      --starts and ends in middle of another date
(6, '2023-06-01 10:00:00.000', '2023-06-01 22:00:00.000');      --starts and ends in middle of same date


WITH DateRanges(Id, FromDateTime, ToDateTime, LevelNo, [Date], DateWiseStartDateTime, DateWiseEndDateTime)
AS
(
    SELECT 
        p.*,
        1,
        CAST(p.FromDateTime AS DATE),
        p.FromDateTime,
        IIF(DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, p.FromDateTime) + 1, 0)) < p.ToDateTime, DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, p.FromDateTime) + 1, 0)),  p.ToDateTime)   
    FROM @tblDateRange p
    UNION ALL
    SELECT 
        c.*,
        p.LevelNo + 1,
        CAST(DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, 1, p.DateWiseStartDateTime)), 0) AS DATE),
        DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, 1, p.DateWiseStartDateTime)), 0),
        IIF(c.ToDateTime < DATEADD(DAY, 1, p.DateWiseEndDateTime), c.ToDateTime, DATEADD(DAY, 1, p.DateWiseEndDateTime))
    FROM @tblDateRange c
    JOIN DateRanges p ON c.Id = p.Id
    WHERE DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, 1, p.DateWiseStartDateTime)), 0) < c.ToDateTime    --date wise start datetime < range to datetime
)
SELECT *
FROM DateRanges
ORDER BY Id, LevelNo
OPTION (MAXRECURSION 30000) --max 32767, no limit 0

SQL how to convert row with date range to many rows with each date

3 Answers3

Linked