Sql Looping 1

[IAMINFO]

Hello everyone,
I need your guidance if possible, the output of my sql statement produces 12 months of data and stops.

I am not sure how to increment the @ReleaseYear by +1 to continue with the loop and retrieve the next 12 months of data, until I reach the @FinalReleaseYear = 2003

Please help if you can or point me in the right direct
Thank you for reading.

DECLARE @ReleaseMonth int
DECLARE @ReleaseYear int
DECLARE @FinalReleaseYear int

SET @ReleaseMonth = 1
SET @ReleaseYear = 2000
SET @FinalReleaseYear = 2003


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]

This is one way.

DECLARE @ReleaseMonth int
DECLARE @ReleaseYear int
DECLARE @FinalReleaseYear int
 
SET @ReleaseMonth = 1
SET @ReleaseYear = 2000 
SET @FinalReleaseYear = 2003

WHILE @ReleaseYear <= @FinalReleaseYear
  Begin 
    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
      
    Set @ReleaseYear = @ReleaseYear + 1
  End

Note that I didn't really do anything with the inner while loop except for format it a little and indent it so it is easier to visualize.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IAMINFO]

gmmastros thank you for trying to help me , I ran the code again it only returns 12 rows it never processes the next year 2001 , thank you again

 

[gmmastros]

Ooopps. That's because we don't reset the inner loops @ReleaseMonth value.

try this:

DECLARE @ReleaseMonth int
DECLARE @ReleaseYear int
DECLARE @FinalReleaseYear int
 
SET @ReleaseMonth = 1
SET @ReleaseYear = 2000 
SET @FinalReleaseYear = 2003

WHILE @ReleaseYear <= @FinalReleaseYear
  Begin 
    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
      
    Set @ReleaseYear = @ReleaseYear + 1
    [!]Set @ReleaseMonth = 1[/!]
  End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

The simplest implementation will be

SELECT      month(DateAdd(mm,Numbers.Number,@StartDate)) AS 'MonthNumber' 
           ,DATENAME(mm,DateAdd(mm,Numbers.Number,@StartDate))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]  
           ,year(DateAdd(mm,Numbers.Number,@StartDate)) AS 'Year' 

from Numbers where Number between 0 and datediff(month, @StartDate, @EndDate)

assuming you have a Numbers table in your database (if you don't, it's easy to create it on the fly, but I suggest to have a permanent Numbers table)

PluralSight Learning Library

 

[IAMINFO]

gmmastros thank you so much , I am still learning and I ran into a wall on this particular sql statement, thanks again for your help

 

[gmmastros]

Markros makes a good point about reducing things. In particular, SQL is notoriously slow at looping (this applies to while loops and cursors).

If you are interested in a non-looping method to get the same data, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, in my query I forgot to JOIN with the main table we're getting the info from. We still may want to join with Months which we first produce as a cte using either calendar or numbers table.

PluralSight Learning Library