T-SQL Statement – Get Total Working Day in a Month

SQL Statement below show you how to using SQL to get total working day in a month.
SQL Total working day in this SQL Statement is exclude Saturday and Sunday as working day.

Below is SQL Statement that get Total Working Day in a month.

DECLARE @my int
DECLARE @myDeduct int
DECLARE @day INT
DECLARE @mydate DATETIME

SET @mydate = getdate()

SET @myDeduct = 0
SET DateFirst 1
— Set it monday=1 (value)

–Saturday and Sunday on the first and last day of a month will Deduct 1
IF (DATEPART(weekday,(DATEADD(dd,-(DAY(@mydate)-1),@mydate))) > 5)
SET @myDeduct = @myDeduct + 1

IF (DATEPART(weekday,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))) > 5)
SET @myDeduct = @myDeduct + 1

SET @my = day(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))

select (((@my/7) * 5 + (@my%7)) – @myDeduct) as Working_Day_per_month

Return
Value = 21 day (for June 2007)
Value = 22 day (for July 2007)

About onlinejt

Blogger

, ,

No comments yet.

Leave a Reply

*