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
0 responses to "Working Day Calculation"
Subscribe to:
Post Comments (Atom)
Post a Comment