Generate range of dates based on other table
baron pampa
Posts: 3
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?
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
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 -
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!
Redgate Software