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)

5 comments:

Ashley said...

WoW nice blog buddy!Actually WoW is my favorite appreciation keyword. I also like to play. Wow Gold. I was looking for info regarding that and saw your website. Its nice and very interesting.

Mind said...

buy Aion Kinah

Good post

Mind said...

buy Aion Kinah

Nice post

Mind said...

wow gold kaufen


Great post

anildbest said...
This comment has been removed by the author.