A common problem is in some cases we have the initial date and end date stored in the table, but we need all range the dates in the period.
Considering the products tables below I will show four techniques to get the result above
DECLARE @Products TABLE ( id INT ,product VARCHAR(10) ,startdate DATE ,enddate DATE ); INSERT @Products (id, product ,startdate ,enddate) VALUES (1, 'ProductA' ,'2016-03-29' ,'2016-04-02' ) ,(2, 'ProductB' ,'2016-05-10' ,'2016-05-12' ) ,(3, 'ProductC' ,'2016-05-01' ,'2016-05-7' ) ;
First using recursive call
WITH cte AS (SELECT id ,product ,startdate ,enddate FROM @Products t UNION ALL SELECT id ,product ,Dateadd(day, 1, startdate) startdate ,enddate FROM cte WHERE startdate <= enddate) SELECT id ,product ,startdate FROM cte ORDER BY product ,startdate OPTION (maxrecursion 0);
The problem of this approuch is if you have more that 100 recursive interactios you need to add
OPTION (maxrecursion 0)
at the end of the query.
Using the Cross Apply
SELECT A.id ,A.product ,B.date FROM @Products A CROSS apply (SELECT TOP (Datediff(day, A.[startdate], A.[enddate])+1) Date= Dateadd(day, -1 + Row_number() OVER (ORDER BY (SELECT NULL)), A.[startdate]) FROM master..spt_values n1 ,master..spt_values n2) B ORDER BY A.product ,B.date;
in this example we use a workaround to quickly create a tally table with dates. using the table master..spt_values we create a tally table with more than 6 million of registers.
Using a traditional tally table with date delimitation
DECLARE @fromdate DATE = '20160101'DECLARE @thrudate DATE = '20171231' ;WITH n AS ( SELECT n FROM (VALUES ( 0 ) ,(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)) , dates AS (SELECT TOP (Datediff (day, @fromdate, @thrudate)+1) [Date]= CONVERT(DATE, Dateadd(day, Row_number() OVER( ORDER BY ( SELECT 1)) - 1, @fromdate)) FROM n AS deka CROSS JOIN n AS hecto CROSS JOIN n AS kilo CROSS JOIN n AS tenK CROSS JOIN n AS hundredK ORDER BY [date]) SELECT t.product ,d.date ,t.id FROM @Products t INNER JOIN dates d ON d.date >= t.startdate AND d.date <= t.enddate ORDER BY product ,date
In this tally table was used dates to limit the processing
Using a physical tally table
Code to create the physical table
DECLARE @fromdate DATE = '20000101'; DECLARE @years INT = 300; WITH n AS (SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)) SELECT TOP (Datediff(day, @fromdate, Dateadd(year, @years, @fromdate))) [Date]= CONVERT(DATE, Dateadd(day, Row_number() OVER( ORDER BY (SELECT 1)) - 1, @fromdate)) INTO dbo.dates FROM n AS deka CROSS JOIN n AS hecto CROSS JOIN n AS kilo CROSS JOIN n AS tenK CROSS JOIN n AS hundredK ORDER BY [date]; CREATE UNIQUE CLUSTERED INDEX ix_dbo_dates_date ON dbo.dates([date]);
Query to retrieve the values
SELECT t.product
,d.date
,t.id
FROM @Products t
INNER JOIN dates d
ON d.date >= t.startdate
AND d.date <= t.enddate [/code
In this technique, we get the best performance but is necessary create a table with the max and min dates before the use and depending of the ranges the dates tables can be bigger than that and compromise the performance.