Search This Blog

Friday, 28 September 2012

Exclude Week End Days From Calendar

Today I found a post at MSDN to exclude Sunday using Datediff(). I worked around the query and found a solution

declare @start datetime,
              @end datetime

set @start = '2006-01-01'
set @end =   '2006-01-08'
;
with calendar(date,isweekday, y, q,m,d,dw,monthname,dayname,w) as
(
select @start ,
case when datepart(dw,@start) in (1,7) then 0 else 1 end,
year(@start),
datepart(qq,@start),
datepart(mm,@start),
datepart(dd,@start),
datepart(dw,@start),
datename(month, @start),
datename(dw, @start),
datepart(wk, @start)
union all
select date + 1,
case when datepart(dw,date + 1) in (1,7) then 0 else 1 end,
year(date + 1),
datepart(qq,date + 1),
datepart(mm,date + 1),
datepart(dd,date + 1),
datepart(dw,date + 1),
datename(month, date + 1),
datename(dw, date + 1),
datepart(wk, date + 1) from calendar where  date + 1<= @end
)
select * into #test from calendar option(maxrecursion 10000)
Select  DATEDIFF(DD,@start,@end)+1-Count(case when #test.isweekday=0 then #test.isweekday end )
from #test where #test.date between @start and @end

You can also find the solution in this way

SELECT startDate,EndDate,DATEDIFF(DD,startDate,EndDate) FROM Production.WorkOrder WHERE ((DATEPART(dw, StartDate) + @@DATEFIRST) % 7) NOT IN (0, 1)