Break up year range into individual records.

Last Post 18 Jul 2010 10:36 AM by russellb. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
GoodFella3993
New Member
New Member

--
18 Jul 2010 05:22 AM
I have a table that has 2 fields with BeginYear and EndYear (Ranges).

I need to break up the year range so that I have individual records and no more range.


Currently:

BeginYear End Year Field1
2000 2003 a
2005 2006 b
2004 2007 c

 

Need to make it like this:

Year Field1
2000 a
2001 a
2002 a
2003 a
2005 b
2006 b
2004 c
2005 c
2006 c
2007 c


Any Ideas?

Thank you.
russellb
New Member
New Member

--
18 Jul 2010 10:36 AM
here's one way:

Declare @min int
Declare @max int
Declare @t table ([year] int, Field1 char(1))

SELECT @min = min(BeginYear), @max = max(EndYear) FROM YourTable;

While @min <= @max
Begin
Set @min = @min + 1
Insert @t SELECT @min, Field1 FROM yourTable WHERE @min BETWEEN BeginYear and EndYear
End

SELECT * FROM @t


Acceptable Use Policy
---