Working Day Calculation

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




CREATE PROCEDURE [dbo].[sp_WorkingDays]
(
    @StartDate    DATETIME,
    @EndDate    DATETIME
)
AS
BEGIN
DECLARE @WeekNumber VARCHAR(50)
--DECLARE @StartDate    DATETIME
--DECLARE @EndDate    DATETIME
DECLARE @StDate        VARCHAR(50)
DECLARE @Yr            VARCHAR(10)
DECLARE    @Month        VARCHAR(10)
DECLARE    @EndMonth        VARCHAR(10)
DECLARE @WDayCount    BIGINT
DECLARE @WDay        BIGINT
DECLARE @NumberOfDays INT
DECLARE @lastDayOfMonth BIGINT
DECLARE @WeekDay    VARCHAR(50)
DECLARE @Counter    BIGINT
DECLARE @Pos        BIGINT
DECLARE @HDayCount    BIGINT

--SET @startdate = '2010-02-20'
--SET @enddate = '2010-03-31'
SET @EndMonth = MONTH(@Enddate)

-- Calculating Working Day Excluding Sunday
SET @NumberOfDays = DATEDIFF(d, @startdate, @enddate) + 1
SET DATEFIRST 7
SET ROWCOUNT @NumberOfDays

DECLARE @numbers TABLE (i INT IDENTITY(0,1), x BIT)
INSERT INTO @numbers SELECT NULL FROM master.dbo.sysobjects a,
master.dbo.sysobjects b, master.dbo.sysobjects c
SET ROWCOUNT 0

SELECT @WDayCount = COUNT(d)
    FROM
        (SELECT DATEADD(dd, i, @startdate) d FROM @numbers) dates
        WHERE NOT DATEPART(dw, d) = 1

-- Calculation For Working Days Excluding Sunday
SET @StDate = @StartDate
DECLARE outer_Cursor CURSOR FOR
    SELECT DATEPART(YEAR, d) AS YEAR, DATEPART(MONTH, d) AS MONTH, COUNT(d) AS 'WorkingDay'
    FROM
        (SELECT DATEADD(dd, i, @startdate) d FROM @numbers) dates
        WHERE NOT DATEPART(dw, d) = 1 GROUP BY DATEPART(YEAR, d), DATEPART(MONTH, d)
        ORDER BY 1, 2
OPEN outer_Cursor
FETCH NEXT FROM outer_Cursor INTO @Yr,@Month,@WDay
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Year wise Month Checking
    IF @Yr = YEAR(@EndDate)
    BEGIN
        IF MONTH(@StDate) <= @EndMonth
        BEGIN
            IF @Pos = 1
            BEGIN
                SET @StDate = CONVERT(DATETIME,'01' + '-' + CONVERT(VARCHAR,MONTH(@StDate)) + '-' + CONVERT(VARCHAR,YEAR(@StDate)),103)
            END
        END
        ELSE
        BEGIN
            SET @StDate = @Enddate
        END
    END
    ELSE
    BEGIN
        IF @Pos = 1
        BEGIN
            SET @StDate = CONVERT(DATETIME,'01' + '-' + CONVERT(VARCHAR,MONTH(@StDate)) + '-' + CONVERT(VARCHAR,YEAR(@StDate)),103)
        END
    END
    -- Calculate The Week No
    SELECT @lastDayOfMonth = DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StDate)+1,0)))
    IF MONTH(@StDate) = @EndMonth
    BEGIN
        SET @lastDayOfMonth = DAY(@Enddate)
    END
   
    SET @Counter = DAY(@StDate)
    WHILE @Counter <= @lastDayOfMonth
    BEGIN
        -- Code to check weather it is holiday
        SELECT @HDayCount=COUNT(*) FROM HoliDayList WHERE HoliDay = @StDate
        IF @HDayCount > 0
        BEGIN
            -- Calling function to get WeekDay Name
            SET @WeekDay = dbo.GetWeekDayNameOfDate(@StDate)
            IF CONVERT(BIGINT,DAY(@StDate)) <= @lastDayOfMonth
            BEGIN
                IF @WeekDay = 'Saturday'
                BEGIN
                    IF MONTH(@StDate) < 10
                    BEGIN
                        SET @Month = '0' + CONVERT(VARCHAR(2),MONTH(@StDate))
                    END
                    ELSE
                    BEGIN
                        SET @Month = MONTH(@StDate)
                    END
                    -- Calling Function to get Week No
                    SET @WeekNumber = dbo.fn_WeekDays(@Month,@StDate)
                    IF @WeekNumber <> 2 OR @WeekNumber <> 4
                    BEGIN
                        SET @WDayCount = @WDayCount - 1
                    END
                END
                ELSE IF @WeekDay <> 'Sunday'
                BEGIN
                    SET @WDayCount = @WDayCount - 1
                END
                SET @StDate = CONVERT(DATETIME,DATEADD(d,1,@StDate),103)
            END
        END
        ELSE
        BEGIN
            -- Calling function to get WeekDay
            SET @WeekDay = dbo.GetWeekDayNameOfDate(@StDate)
            IF CONVERT(BIGINT,DAY(@StDate)) <= @lastDayOfMonth
            BEGIN
                IF @WeekDay = 'Saturday'
                BEGIN
                    IF MONTH(@StDate) < 10
                    BEGIN
                        SET @Month = '0' + CONVERT(VARCHAR(2),MONTH(@StDate))
                    END
                    ELSE
                    BEGIN
                        SET @Month = MONTH(@StDate)
                    END
                    -- Calling Function to get Week No
                    SET @WeekNumber = dbo.fn_WeekDays(@Month,@StDate)
                    IF @WeekNumber = 2 OR @WeekNumber = 4
                    BEGIN
                        SET @WDayCount = @WDayCount - 1
                    END
                END
                SET @StDate = CONVERT(DATETIME,DATEADD(d,1,@StDate),103)
            END
        END
        SET @Counter = @Counter + 1
    END
    SET @Pos = 1
    SET @WeekNumber = 1
FETCH NEXT FROM outer_Cursor INTO @Yr,@Month,@WDay
END
CLOSE outer_Cursor
DEALLOCATE outer_Cursor
    SELECT @WDayCount AS TotalWorkingDays, (@WDayCount * 9) AS TotalWorkingHours
END