Mihai Hangiu Mihai Hangiu - 7 months ago 6
SQL Question

Generates all dates between two dates stored in a table

SQL Server 2014

I need to generate all dates between the start and end dates from a table #data. Table #data contains several rows with start and end date.

CREATE TABLE #data (
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
Opt INT NOT NULL)

insert into #data values('2016-04-10', '2016-04-12', 2)
insert into #data values('2016-04-10', '2016-04-15', 3)
insert into #data values('2016-05-10', '2016-05-12', 4)


Table #data may contain hundreds of rows.

My final selection shall contain:

2016-04-10 2
2016-04-11 2
2016-04-12 2
2016-04-10 3
2016-04-11 3
2016-04-12 3
2016-04-13 3
2016-04-14 3
2016-04-15 3
2016-05-10 4
2016-05-11 4
2016-05-12 4


I currently have this, but I have to extend the selection for all the rows in #data:

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

SET @StartDateTime = '2015-01-01'
SET @EndDateTime = '2015-01-12';

--delete from #tmp

;WITH DateRange(DateData) AS
(
SELECT @StartDateTime as Date
UNION ALL
SELECT DATEADD(d,1,DateData)
FROM DateRange
WHERE DateData < @EndDateTime
)
INSERT INTO #tmp SELECT DateData, 1 -- instead of 1 shall be Opt
FROM DateRange
OPTION (MAXRECURSION 0)

select * from #tmp


Thank you.

jpw jpw
Answer

Maybe this is what you want?

;WITH DateRange(DateData, EndDate, Opt) AS 
(
    SELECT StartDate, EndDate , Opt FROM #data
    UNION ALL
    SELECT DATEADD(d,1,DateData), EndDate, Opt
    FROM DateRange 
    WHERE DateData < EndDate
)
SELECT DateData, Opt
FROM DateRange
ORDER BY opt, DateData
OPTION (MAXRECURSION 0)

I would consider generating a suitable calendar table though. If you had one you could accomplish the result you want a lot more efficiently (by using joins with the calendar table).

See these articles for examples and more information:

http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Comments