/*
DESCRIPTION:: Used to get total working days excluding sunday and 5th Saturday of any monthhs.
Created By : NITISH KUMAR
DATE:: 21 Aug, 14
SELECT dbo.fn_getWorkingDays('1 Mar 2014','6 May 2014') as WD
*/
Create function fn_getWorkingDays (@DateFrom smalldatetime, @DateTo smalldatetime)
Returns varchar(30)
AS
BEGIN
DECLARE @FMonth varchar(20), @TMonth varchar(20), @FYear varchar(10), @TYear varchar(10), @WD bigint, @Count varchar(10), @tot varchar(10)
--SET @DateFrom='1 Mar 2014';
--SET @DateTo='31 May 2014';
--SET NOCOUNT ON
Declare @dates Table(totdates datetime)
WITH T(date)
AS
(
SELECT @DateFrom
UNION ALL
SELECT DateAdd(day,1,T.date) date FROM T WHERE T.date < @DateTo
) Insert INTO @dates Select * FROM T OPTION (MAXRECURSION 32767);
--SELECT * FROM @dates OPTION (MAXRECURSION 32767);
SET @Count='0';SET @tot='0';
DECLARE CUR CURSOR FOR SELECT distinct DATEPART(YYYY,totdates) M,DATEPART(MM,totdates) Y FROM @dates OPTION (MAXRECURSION 32767);
OPEN CUR
Fetch NEXT FROM CUR INTO @FYear, @FMonth
While @@FETCH_STATUS=0
BEGIN
SELECT @Count=Count(*) FROM @dates Where DATENAME(DW,totdates)='Saturday' and DatePart(MM,totdates)=@FMonth and DATEPART(YYYY,totdates) =@FYear OPTION (MAXRECURSION 32767);
IF(CAST(@Count as bigint) =5)
BEGIN
IF(ISNULL(@tot,0) ='0')
SET @tot = 1
ELSE
SET @tot = Cast(@tot as bigint) + 1;
END
Fetch NEXT FROM CUR INTO @FYear, @FMonth
END
CLOSE CUR
DEALLOCATE CUR
SELECT @WD=Count(*) FROM @dates OPTION (MAXRECURSION 32767);
SET @Count='';
SELECT @Count=Count(*) FROM @dates Where DATENAME(DW,totdates)='Sunday' OPTION (MAXRECURSION 32767);
SET @tot = Cast(@tot as bigint) + Cast(@Count as bigint)
SET @tot= @WD- Cast(@tot as bigint)
--SET NOCOUNT OFF
--PRINT @tot
return @tot
END
NOTE::
| The MAXRECURSION query hint specifies the maximum number of recursions allowed for a query. The number of recursions is a non-negative integer between 0 and 32,767. When 0 is specified, no limit is applied. |
If the MAXRECURSION query hint is not specified, the default limit is 100. When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.
CHEERS!!!
