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.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
Scott Pletcher
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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)
),
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#
Open in new window