Sunday, November 17, 2013

CTE, Recursive Call, Find out number of working days between a date range

http://www.sqlservercentral.com/Forums/Topic779830-338-1.aspx


DECLARE @STARTDATE datetime;
DECLARE @EntDt datetime;
set @STARTDATE = '01/01/2009';
set @EntDt = '12/31/2009';
declare @dcnt int;
;with DateList as  
 (  
    select @STARTDATE DateValue  
    union all  
    select DateValue + 1 from    DateList    
    where   DateValue + 1 < convert(VARCHAR(15),@EntDt,101)  
 )  
 select count(*) as DayCnt from (  
  select DateValue,DATENAME(WEEKDAY, DateValue ) as WEEKDAY from DateList
  where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )    
  )a
option (maxrecursion 365);