Break up year range into individual records.

Last Post 18 Jul 2010 11:36 AM by russellb. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
GoodFella3993
New Member
New Member

--
18 Jul 2010 06: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 11: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
You are not authorized to post a reply.

Acceptable Use Policy