Generate range of dates based on other table

baron pampa · January 10, 2016 4:39AM

Hello!

I've got two tables: The first one has to date attributes, the Beginning Date and End Date. I would like every row in the second to correspond to one of the dates in the range defined in the first table - but despite reading documentation I'm unsure how to do so. Would you kindly give me a hint?

Rob C · January 11, 2016 11:05AM

Hi!

There are probably a number of different ways of doing this, but here's one off the top of my head.

Suppose that we have a table named "dates", which contains two columns - "startdate" and "enddate". This is where we'll get the start and end dates from. Now, for the column we want to generate the dates for, choose a "SQL Statement" generator and set the connection to point to your database. Then you can put something like this in the SQL Source textbox -

DECLARE @StartDateTime DATE
DECLARE @EndDateTime DATE

SET @StartDateTime = (SELECT TOP 1 startdate FROM dates);
SET @EndDateTime = (SELECT TOP 1 enddate FROM dates);

WITH DateRange(DateData) AS 
(
    SELECT @StartDateTime as Date
    UNION ALL
    SELECT DATEADD(d,1,DateData)
    FROM DateRange 
    WHERE DateData &lt; @EndDateTime
)
SELECT DateData
FROM DateRange
OPTION (MAXRECURSION 0)

This will generate all the dates in between the values stored in the dates table. There may well be a better SQL query to do this - I put it together quite quickly.

I hope this helps!

baron pampa · January 12, 2016 2:58AM

Thank you very much:)

Generate range of dates based on other table

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk