Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

SQL Recursion

Hello there I am trying to create a recusion that creates an entry for a weekly  start and end Friday to Friday


;WITH cteIDate AS (
SELECT  dateadd(day, (@@datefirst - datepart(weekday, dateadd(year, YEAR(Getdate()) - 1900, 0)) + (10 - @@datefirst) * 2) % 7, dateadd(year, YEAR(Getdate()) - 1900, 0)   ) as  StartDate
, dateadd(day, (@@datefirst - datepart(weekday, dateadd(year, YEAR(Getdate()) - 1900, 0)) + (10 - @@datefirst) * 2) % 7, dateadd(year, YEAR(Getdate()) - 1900, 0)   ) + 7  as EndDate
UNION ALL
SELECT dateadd(day , 7, StartDate) AS StartDate, dateadd(day , 7, EndDate) AS EndDate
FROM cteIDate
WHERE Year(dateadd(day , 7, EndDate)) < Year(Getdate()) + 2 

)

SELECT * from cteIDate ci
OPTION (MAXRECURSION 52)
)

SELECT * from cteIDate ci
OPTION (MAXRECURSION 52)

Open in new window


Getting this error
Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 52 has been exhausted before statement completion.

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Leo Torres

ASKER

yea just saw that
;WITH cteIDate AS (
SELECT  dateadd(day, (@@datefirst - datepart(weekday, dateadd(year, YEAR(Getdate()) - 1900, 0)) + (10 - @@datefirst) * 2) % 7, dateadd(year, YEAR(Getdate()) - 1900, 0)   ) as  StartDate
, dateadd(day, (@@datefirst - datepart(weekday, dateadd(year, YEAR(Getdate()) - 1900, 0)) + (10 - @@datefirst) * 2) % 7, dateadd(year, YEAR(Getdate()) - 1900, 0)   ) + 7  as EndDate
UNION ALL
SELECT dateadd(day , 7, StartDate) AS StartDate, dateadd(day , 7, EndDate) AS EndDate
FROM cteIDate
WHERE Year(dateadd(day , 7, EndDate)) < Year(Getdate()) + 2 

)

SELECT * from cteIDate ci
OPTION (MAXRECURSION 104)

Open in new window

thanks
But recursion is a very inefficient way to loop / gen numbers.  And a simple date calc can avoid any issues/concerns with datefirst.  The code below is far more efficient and works under any/all datefirst settings.


;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
    CROSS JOIN cteTally10 c3
),
cteJan07 AS (
    SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 6) AS Jan07
),
cteJanFirstFriday AS (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 4, Jan07) % 7, Jan07) AS JanFirstFriday
    FROM cteJan07
)
SELECT DATEADD(DAY, 7 * t.number, JanFirstFriday) AS StartDate,
    DATEADD(DAY, 7 * t.number + 7, JanFirstFriday) AS EndDate
FROM cteTally1000 t
CROSS JOIN cteJanFirstFriday
WHERE t.number <= 110 AND YEAR(DATEADD(DAY, 7 * t.number, JanFirstFriday)) <= YEAR(GETDATE()) + 1
Great where can i add a WeekID here?
What ID do you want?  A simple week#?  If so, add this to the SELECT:

    DATEPART(DAYOFYEAR, DATEADD(DAY, 7 * t.number, JanFirstFriday)) / 7 + 1 AS Week#