Friday, June 15, 2007

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)