Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The maximum recursion 100 has been exhausted before statement completion Expand / Collapse
Author
Message
Posted Monday, August 31, 2009 6:22 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:36 AM
Points: 22, Visits: 118
Dear Expert,

I want to find the number of Working days in a year,

so i passed the two date values to startdate and enddates respectively,

When i giving input as

set @STARTDATE = '01/01/2009';
set @EntDt = '03/31/2009';

i'm getting result as 63 it's fine, but when i give End date as '12/31/2009'; getting the following error

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

i had attached my code with this post,

guidance needed

DECLARE @STARTDATE datetime;  
DECLARE @EntDt datetime;
set @STARTDATE = '01/01/2009';
set @EntDt = '12/31/2009';
declare @dcnt int;
;with DateList as
(
select cast(@STARTDATE as datetime) DateValue
union all
select DateValue + 1 from DateList
where DateValue + 1 < convert(VARCHAR(15),@EntDt,101)
)

select count(*) as DayCnt from (
select DateValue,DATENAME(WEEKDAY, DateValue ) as WEEKDAY from DateList
where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
)a


Msg 530, Level 16, State 1, Line 6
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


thanks in advance.


Known Is A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #779830
Posted Monday, August 31, 2009 6:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
Maybe not the greatest approach to the solution, but you could just add WITH MAXRECURSION = 365 query hint. Recursion is limited to ~32,000 calls.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #779854
Posted Tuesday, September 1, 2009 3:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:18 AM
Points: 1,180, Visits: 2,650
Here's an alternative that doesn't use a recursive CTE to generate the dates.

DECLARE @d1 datetime, @d2 datetime
DECLARE @n int

SELECT
@d1 = '20090831',
@d2 = '20090904',
@n = DATEDIFF(day,@d1,@d2)

SELECT
DATENAME(dw,date)
FROM
(
SELECT TOP (@n)
date = DATEADD( day,
ROW_NUMBER()OVER (ORDER BY t1.name)-1,
@d1)
FROM
sys.columns t1, sys.columns t2
) d
WHERE
DATENAME(dw,date) NOT IN ('Saturday','Sunday')

I notice that your solution excludes the end date (@EndDt) in the count, is that intentional?
The above solution does the same. If you need to include the end date change the line:
        @n = DATEDIFF(day,@d1,@d2)

to
        @n = DATEDIFF(day,@d1,@d2)+1

Alternative solutions could use either a Tally or Calendar table.


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #780513
Posted Tuesday, September 1, 2009 4:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 982, Visits: 1,100
To add on Nigel's post, you enhance the query in the following way:

DECLARE @d1 datetime, @d2 datetime
DECLARE @n int

SELECT
@d1 = '20090831',
@d2 = '20090930',
@n = DATEDIFF(day,@d1,@d2)

SELECT
CAST(DATENAME(dw,date) AS Nvarchar(15)) + ', ' + CASE RIGHT(CAST(DATEPART(dd, date) AS Nvarchar(2)), 1)
WHEN 1 THEN CAST(DATEPART(dd, date) AS Nvarchar(2)) + 'st'
WHEN 2 THEN CAST(DATEPART(dd, date) AS Nvarchar(2)) + 'nd'
WHEN 3 THEN CAST(DATEPART(dd, date) AS Nvarchar(2)) + 'rd'
ELSE
CAST(DATEPART(dd, date) AS Nvarchar(2)) + 'th'
END
FROM
(
SELECT TOP (@n)
date = DATEADD( day,
ROW_NUMBER()OVER (ORDER BY t1.name)-1,
@d1)
FROM
sys.columns t1, sys.columns t2
) d
WHERE DATENAME(dw,date) NOT IN ('Saturday','Sunday')


Post #780552
Posted Tuesday, September 1, 2009 4:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:18 AM
Points: 1,180, Visits: 2,650
Apologies I posted a slightly incorrect query, given your requirements. It should have:

       COUNT(*)

in place of the:

       DATENAME(dw,date)



--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #780560
Posted Tuesday, September 1, 2009 5:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:18 AM
Points: 1,180, Visits: 2,650
Have been pondering this and have come up with an alternative that uses a Tally table:

DECLARE @d1 datetime, @d2 datetime

SELECT
@d1 = '20090101',
@d2 = '20090331'

SELECT
count(N)
FROM
Tally
WHERE
N <= DATEDIFF(day,@d1,@d2)+1
AND
(DATEPART(dw,DATEADD(dd,n-1,@d1))+@@DATEFIRST)%7 BETWEEN 2 AND 6



Advantages over previous solutions, as I see it, are:
- Doesn't use CTE's (recursive or otherwise) or ROW_NUMBER, so can be used in SQL 2000.
- The query itself is a lot simpler to read
- Looking at the query plan 92% of the time is spent doing a clustered index seek.
- Use of @@DATEFIRST and Modulus operator makes the solution multi-culture
- Use of BETWEEN rather than NOT IN is significantly quicker


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #780567
Posted Tuesday, September 1, 2009 5:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:18 AM
Points: 1,180, Visits: 2,650
Jonathan,

Minor problem, your query doesn't get the 11th, 12th and 13th correct, you get 11st, 12nd and 13rd instead. Oops!


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #780580
Posted Tuesday, September 1, 2009 6:03 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 9, 2011 2:49 AM
Points: 536, Visits: 412
@Grant Fritchey

Yes u r right!

it just need to add 'Option (maxrecursion 365)' (100 is the default limit for CTE, one can set it upto 32767 with 'maxrecursion')

DECLARE @STARTDATE datetime; 
DECLARE @EntDt datetime;
set @STARTDATE = '01/01/2009';
set @EntDt = '12/31/2009';
declare @dcnt int;
;with DateList as
(
select @STARTDATE DateValue
union all
select DateValue + 1 from DateList
where DateValue + 1 < convert(VARCHAR(15),@EntDt,101)
)
select count(*) as DayCnt from (
select DateValue,DATENAME(WEEKDAY, DateValue ) as WEEKDAY from DateList
where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
)a
option (maxrecursion 365);



"Don't limit your challenges, challenge your limits"
Post #780605
Posted Tuesday, September 1, 2009 10:14 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:36 AM
Points: 22, Visits: 118
Thanks Grant Fritchey & kruti, i Got the solution from your guidance

once again thanks


Known Is A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #781203
Posted Wednesday, September 2, 2009 1:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:18 AM
Points: 1,180, Visits: 2,650
Sure, setting MAXRECUSRION will work, but at what expense?
Grant even said in his post that it wasn't the greatest approach.
I would avoid recursive CTEs if there is a more efficient solution, which in this case there is.



--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #781253
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse