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

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9b3efb7f-2015-432f-8624-c78a5c535f1c

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)