Generate range of dates based on other table

baron pampabaron pampa Posts: 3
edited January 12, 2016 2:58AM in SQL Data Generator
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?

Comments

  • Rob CRob C Gold 2 Posts: 418 Gold 2
    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 < @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!
    Software Engineer
    Redgate Software
  • Thank you very much:)
Sign In or Register to comment.