Looping 1

[IAMINFO]

Hello in the code below I am manually plugging in the year
to insert 12 months of data into a table.

I would like to increment the year by 1 each time 12 months of data is inserted until I get to 2011. Not quite sure how to do this. Thank you for reading and taking time


DECLARE @MoveInYear varchar(10)
DECLARE @MoveInMonthNumber int
SET @MoveInYear = '1997'
SET @MoveInMonthNumber = 1

WHILE @MoveInMonthNumber < 13
BEGIN

INSERT INTO DailyAdmits

SELECT COUNT(Offender_Number)Admitted , MoveInDay, MoveInMonth,MoveInYear,MoveInDayName

FROM Housing

WHERE [MoveInYear]=@MoveInYear
AND MICLASS='LODGE'
AND [MoveInMonthNumber]=@MoveInMonthNumber
GROUP BY MoveInDay,MoveInMonth,MoveInYear,MoveInDayName
ORDER BY MoveinDay


SET @MoveInMonthNumber = @MoveInMonthNumber + 1
END

 

[gmmastros]

Instead of looping on an integer, why don't you just loop on a date instead. For example....

Declare @StartDate DateTime
Set @StartDate = '19970101'

While @StartDate <= '20111201'
  Begin
	
	Select @StartDate, Year(@StartDate), Month(@StartDate)

    Set @StartDate = DateAdd(Month, 1, @StartDate)
  End

Notice that the date is incremented by one month for each iteration of the loop, and you can easily get the year and month from the date, so I think this should work out pretty well for.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom