Leo Torres
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
Getting this error
;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)
Getting this error
Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 52 has been exhausted before statement completion.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0)
),
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
ASKER
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#
DATEPART(DAYOFYEAR, DATEADD(DAY, 7 * t.number, JanFirstFriday)) / 7 + 1 AS Week#
ASKER
Open in new window