While Loop 3

[IAMINFO]

The statement below returns data for the 12 months of year 2000. I am not sure how to increment the year by 1 and loop through again to insert data for year 2001.

Any thoughts or examples would be appreciated, thank you for reading.


DECLARE @ReleaseMonth int
DECLARE @ReleaseYear varchar(10)
SET @ReleaseMonth = 1
SET @ReleaseYear = '2000'

WHILE @ReleaseMonth < 13
BEGIN

--INSERT INTO ALOS



SELECT @ReleaseMonth AS'MonthNumber'
,DATENAME(mm,DateAdd(mm,@ReleaseMonth,-1))AS Month
,COUNT(AR.Offender_Number)Released
,SUM(LengthOfStay) AS [TotalDays]
,SUM(CONVERT(DECIMAL(10,2),LengthOfStay))/Count(AR.Offender_Number)AS [Average Length of Stay]
,@ReleaseYear AS 'Year'




FROM Admission_Release AR INNER JOIN
Offenders O ON AR.Offender_Number = O.Offender_Number
WHERE ReleaseMonth= DATENAME(mm,DateAdd(mm,@ReleaseMonth,-1)) AND ReleaseYear=@ReleaseYear

AND AR.Release_Age >= 18

SET @ReleaseMonth = @ReleaseMonth +1

END

 

[gmmastros]

Instead of looping on an integer, you could loop on a date instead. Something like this:

Declare @LoopDate DateTime
Declare @LoopEnd DateTime

Set @LoopEnd = '20050101'
Set @LoopDate = '20000101'

While @LoopDate <= @LoopEnd
  Begin
    Select Month(@LoopDate) As MonthNumber,
           DateName(Month, @LoopDate) As MonthName,
           Year(@LoopDate) As YearNumber

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

-George

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

 

[simian336]

DECLARE @ReleaseMonth int
DECLARE @ReleaseYear varchar(10)
declare @loop bit
SET @ReleaseMonth = 1
SET @ReleaseYear = '2000'
set @loop=1

WHILE @loop=1
BEGIN
--INSERT INTO ALOSSELECT
Select @ReleaseMonth AS'MonthNumber'
,DATENAME(mm,DateAdd(mm,@ReleaseMonth,-1))AS Month
,COUNT(AR.Offender_Number)Released
,SUM(LengthOfStay) AS [TotalDays]
,SUM(CONVERT(DECIMAL(10,2),LengthOfStay))/Count(AR.Offender_Number)
AS [Average Length of Stay]
,@ReleaseYear AS 'Year'
FROM Admission_Release AR
INNER JOIN Offenders O
ON AR.Offender_Number = O.Offender_Number
WHERE ReleaseMonth= DATENAME(mm,DateAdd(mm,@ReleaseMonth,-1))
AND ReleaseYear=@ReleaseYear AND AR.Release_Age >= 18S

SET @ReleaseMonth = @ReleaseMonth +1

if @ReleaseMonth = 13 and @ReleaseYear = '2001'
set @loop=0
if @ReleaseMonth = 13 and @ReleaseYear = '2000'
SET @ReleaseYear = '2001'
END

Simi

 

[markros]

Alternative solution by using Numbers table:

declare @StartDate datetime, @EndDate datetime

set @StartDate = '20000101'

set @EndDate = '20050101'

select identity(bigint,0,1) as Number into Tally 
from master.dbo.spt_Values T1, master.dbo.spt_Values T2 where T1.Type = 'P' and T2.type = 'P'

select dateadd(day, Number, @StartDate) as [DateFld] 
from Tally where dateadd(day,Number, @StartDate) < =@EndDate

PluralSight Learning Library