Get dates between periods

Question

I have a table containing the following data

periodID periostart periodend
1        01-01-2012 10-01-2012
2        11-01-2012 01-04-2012

I would like for a query to return something like this. periodID date

1        01-01-2012
1        02-01-2012
1        03-01-2012
etc.
1        09-01-2012
2        11-01-2012
2        12-01-2012
etc.
2        31-03-2012

Since I have approx 100,000 rows containing periods, I would like to work on a solution that would not influence performance (cursors, loops). Is it possible to get the result I want without using cursors or loops?

Thanks for participatin.

I got this solution so far

create table #p (id int, periodstart smalldatetime, periodend smalldatetime );
insert into #p values 
(1, '2012-01-01', '2015-01-10')
insert into #p values
(2, '2012-04-10', '2015-11-20');


SELECT TOP 366 --aprox one year
        IDENTITY(INT,0,1) AS N
   INTO #Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2



SELECT DATEADD(day, T.N, periodstart) AS [Date]
FROM #p
cross join #tally T
WHERE (T.N >= 0 AND T.N < DATEDIFF(day, periodstart, periodend))
ORDER BY [Date]

Answer 1

My first Itzik-Style Cross-Join:

declare @longestPeriod int
set @longestPeriod = 1000  --you should calculate it with a single query

create table #p (id int, periostart date, periodend date );
insert into #p values 
(1, '20120101', '20120110'),
(2, '20120110', '20120120');

WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
  E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows
  E8(N) AS (SELECT 1 FROM E4 a, E4 b),  -- 1*10^8 or 100,000,000 rows
  NN as (SELECT top(@longestPeriod) ROW_NUMBER() 
                OVER (ORDER BY (SELECT NULL)) as N  FROM E8 )
 select 
   id,dateadd( dd, NN.N , periostart) as aDay
 from
   #p
 cross join
   NN
 where NN.N between 0 and datediff( dd, periostart, periodend )

Each day I learn something.

Itzik-Style Cross-Join:

What's really amazing about this bad-boy is that is produces ZERO READS. Absolutely none, nada, nil.

Please, John, post performance test!!!

Answer 2

Here it is:

create table #p (id int, periostart date, periodend date );
insert into #p values 
(1, '20120101', '20120110'),
(2, '20120110', '20120120');


with cte as (
 select
   id, periostart as aDay
 from
   #p
 union all
 select
   cte.id, dateadd( day, 1, cte.aDay) as aDay
 from
   #p
 inner join
   cte on #p.id = cte.id 
 where
   cte.aDay < #p.periodend
)
select * from cte

Results:

id aDay          
-- ------------- 
1  2012-01-01 00:00:00
2  2012-01-10 00:00:00
...
2  2012-01-17 00:00:00
2  2012-01-18 00:00:00
2  2012-01-19 00:00:00
2  2012-01-20 00:00:00
1  2012-01-02 00:00:00
1  2012-01-03 00:00:00
...
1  2012-01-08 00:00:00
1  2012-01-09 00:00:00
1  2012-01-10 00:00:00

Explanation: I use CTE recursion to get new date adding 1 day to current date limiting to end period. Difficult is to get last generated date in recursion join, I solve this with an OVER clause. I think that is a nice query.

EDITED due OP comment

I have posted this approach because OP ask for a solution with no loops and no cursors. I don't know any other way to write a sql sentence without loops or cursors that this one.

Also, I think that the right approach to generate dates is a cursor over periods table with inner loop or with Itzik-Style Cross-Join.

Answer 3

Not entirely sure I understand, but I think you want to use the BETWEEN operator. Something like

SELECT PeriodID, @Date FROM Periods WHERE @Date BETWEEN periodstart AND periodend

or if you are getting the date through joining to a table containing the dates, use the BETWEEN in the join

SELECT periodTable.PeriodID, dateTable.Date 
FROM periodTable 
    INNER JOIN dateTable ON dateTable.Date BETWEEN periodTable.periodstart AND periodTable.periodEnd

In this case you would have a table full of every date from earliest possible to latest possible and join the tables as shown above. A table like this could be created like so:

CREATE TABLE dateTable(ID int identity(1,1), Date datetime NOT NULL)
Declare @d datetime

set @d=CONVERT(datetime, '1/1/1990')--start date

While @d<=CONVERT(datetime, '1/1/2020')--enddate
Begin
Insert into dateTable values (@d)
set @d=DATEADD(dd, 1, @d)
End