0

I need help building a loop in my stored procedure, basically i want it too loop from 2005 till the current year.

 ALTER PROCEDURE [dbo].[testt1]
 as
DECLARE @YearToGet int;
SET @YearToGet = 2005;

WITH Years AS (
    SELECT DATEPART(year, GETDATE()) [Year]
    UNION ALL
    SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet
)

SELECT     TOP (100) PERCENT DIVISION, DYYYY, SUM(APRICE) AS Sales, SUM(PARTY) AS PAX, SUM(NetAmount) AS NetSales, SUM(InsAmount) 
                      AS InsSales, SUM(CancelRevenue) AS CXSales, SUM(OtherAmount) AS OtherSales, SUM(CXVALUE) AS CXValue
FROM         dbo.B101BookingsDetails AS B101BookingsDetails 


WHERE    Booked <= CONVERT(int,DateAdd(year, (SELECT * FROM Years) - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 0)))
  and DYYYY = @YearToGet
GROUP BY  DYYYY, DIVISION

ORDER BY DIVISION,  DYYYY




OPTION (MAXRECURSION 0) -- this avoids hitting the recursion limit in the CTE

and i could really used a stored procedure website that goes into more detail then just the simple select statements

Improve this question
7
  • my loop isn't working, i want to run the select statement multiple until the year reaches the current year Commented Jan 4, 2011 at 21:35
  • 2
    First of all: why not just use YEAR(GETDATE()) instead of the oddball DATENAME() approach..... also: you're never updating your @YearToGet or year value - I would think this ends in either a non-loop or an endless loop.... Commented Jan 4, 2011 at 21:37
  • So what do you really want to do here?? Try to explain what you're trying to achieve..... this is not clear from just a snippet of code.... Commented Jan 4, 2011 at 21:37
  • What error(s) are you getting? Commented Jan 4, 2011 at 21:37
  • o damn hahah im retarded, my major problem is i am getting a syntax error at begin Commented Jan 4, 2011 at 21:37

3 Answers 3

Reset to default
4

This is not directly an answer, but the code cannot be posted in a readible fashion in a comment, so I think this should be okay here:

Don't loop in SPs, rather use a CTE to generate the numbers you need.

DECLARE @YearToGet int;
SET @YearToGet = 2005;

WITH Years AS (
    SELECT DATEPART(year, GETDATE()) [Year]
    UNION ALL
    SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet
)
SELECT * FROM Years -- join here with your query
OPTION (MAXRECURSION 0) -- this avoids hitting the recursion limit in the CTE

Edit: Try this

WITH  Years
          AS (
              SELECT DATEPART(year, GETDATE()) [Year]
              UNION ALL
              SELECT [Year]-1
                FROM Years
                WHERE [Year] > @YearToGet
             )
    SELECT DIVISION, DYYYY, SUM(APRICE) AS Sales, SUM(PARTY) AS PAX, SUM(NetAmount) AS NetSales, SUM(InsAmount) AS InsSales, SUM(CancelRevenue) AS CXSales, SUM(OtherAmount) AS OtherSales, SUM(CXVALUE) AS CXValue
      FROM dbo.B101BookingsDetails 
      JOIN Years yr ON DYYYY = yr.[Year]
      WHERE Booked <= CONVERT(int, DATEADD(year, DYYYY-YEAR(GETDATE()), DATEADD(day, DATEDIFF(day, 2, GETDATE()), 0)))
      GROUP BY DYYYY, DIVISION
      ORDER BY DIVISION, DYYYY
    OPTION (MAXRECURSION 0);
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

so instead of looping i can set @ year to get as many years, but how will it update for next year
Just join your SELECT statement with the Years CTE as shown here, or do a subquery if you like better (this would require little change in your query). Your current code is flawed because it will return an arbitrary number of result sets, which is most certainly not what you're after.
When you're doing SQL always think in SETS instead of row by row (most programming languages). Most, if not all loops in SQL can be rewritten to use only SQL. Loops in SQL are a performance hit.
Thanks clyc for the support... ;-) - and to add to this, since the advent of recursive CTE's, generating data on the fly to be used for set operations has become very easy and efficient.
i agree with everything but the easy part i can't even join my statement
|
3

Ok written to not use recursive CTE :) Since you are already storing the year in the B101BookingsDetails table, just get the list of years that is greater than the year you are looking for from there.

CREATE PROCEDURE dbo.testt1
(
    @YearToGet int = 2005
)
AS

    WITH Years AS (
        SELECT DISTINCT DYYYY as year
        FROM    dbo.B101BookingsDetails
        WHERE DYYYY >= @YearToGet
    )   
    SELECT  TOP (100) PERCENT DIVISION, DYYYY, SUM(APRICE) AS Sales, SUM(PARTY) AS PAX, SUM(NetAmount) AS NetSales, 
                SUM(InsAmount) AS InsSales, SUM(CancelRevenue) AS CXSales, SUM(OtherAmount) AS OtherSales, SUM(CXVALUE) AS CXValue
        FROM    dbo.B101BookingsDetails 
                INNER JOIN Years
                    ON B101BookingsDetails.DYYYY = Years.Year
        WHERE   Booked  <= CONVERT(int,DateAdd(year, Years.Year - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 0)))
        GROUP BY DYYYY, DIVISION
Improve this answer

1 Comment

what if i wanted to change my booked<= convert(int, @date) but the year would have to be different each time for example if the user puts in 1/5/2011 , it would run the query for 1/5/2010, 1/5/2009,1/5/2008.... or if they put 1/10/2011 it would do 1/10/2010, 1/10/2009 ...
0

You need to increment @YearToGet at the end of the loop.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.