Tuesday, April 14, 2009

Counting Weekends Between Date Range

I encountered a requirement that I need to count the number of Weekends, number of Saturdays and number of Sundays between two date range.
Here's what I got so far...
declare @startdate as datetime, @enddate as datetime

select  @startdate = '12-26-2008', @enddate = '01-26-2009'

select
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SATURDAY', 'SUNDAY') then 1
 else 0
end) as 'Number of WeekEnd',
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SUNDAY') then 1
 else 0
end) as 'Number of Sunday',
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SATURDAY') then 1
 else 0
end) as 'Number of Saturday'
from
(select top 365 colorder - 1 as days
 from master..syscolumns where id = -519536829
order by colorder) x
where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0
Here's the resultset:
Number of WeekEnd  Number of Sunday  Number of Saturday
-----------------  ----------------  ------------------
   10                  5                 5
Any suggestions to improve this code is always welcome...


~~CK

No comments:

Post a Comment