troubleshooting Question

SQL Recursion

Avatar of Leo Torres
Leo TorresFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
6 Comments1 Solution142 ViewsLast Modified:
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)

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
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros