Splitting a date range
-
I was hoping somebody could point me in the right direction with the below problem:
I have a result set.
Field1 StartYear EndYear
------ --------- -------
1 2007 2008
2 2006 2009
3 2003 2004
I want to split the date range into individual years.
Field1 IndividualYear
------ --------------
1 2007
1 2008
2 2006
2 2007
2 2008
2 2009
3 2003
3 2004
In other words, SELECT Field1, <something> AS IndividualYear FROM ...
-
Using a numbers table
SELECT Field1,Number AS IndividualYear
FROM mytable
INNER JOIN Numbers ON Number BETWEEN StartYear AND EndYear
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark - Great use of the Tally table! Using a number table here makes this a quick/easy solution.
Mattie - just in case you need more info on Tally/Numbers tables, here's a great read from Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/62867/.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply