Sunday, September 30, 2012

SQL Table for Work Days

I was recently working on some reports using SQL and SSRS to find the velocity of my team. I found that the standard DATEDIFF(d, start, end) which includes weekends just wasn’t going to cut it.

So… I created my own table…

 

   1:  DECLARE @Result TABLE (CalendarDate DATETIME, WorkDay BIT)
   2:   DECLARE @startDate DATE = '2012-1-1'
   3:   DECLARE @endDate DATE = '2015-12-31'
   4:   Declare @CurrentDate datetime
   5:   DECLARE @workday BIT 
   6:    Set @CurrentDate=DATEADD(day, -1,@StartDate)
   7:    While @CurrentDate<=@EndDate
   8:    Begin
   9:      Select @CurrentDate= DateAdd(dd,1,@CurrentDate)
  10:      SELECT @workday = CASE WHEN datename(dw,@CurrentDate) IN('Saturday','Sunday') THEN 0 ELSE 1 END;
  11:      Insert Into @Result Values (@CurrentDate, @workday)
  12:    End
  13:    
  14:    SELECT *
  15:    INTO mwg.Calendar
  16:    FROM @result
  17:    
  18:  SELECT * FROM mwg.Calendar



 


And then I created my own DIFF function


   1:  CREATE FUNCTION [mwg].[WorkdaysDiff](@date1 DateTime, @date2 DATeTIme) RETURNS INT 
   2:  AS 
   3:   
   4:  BEGIN 
   5:  DECLARE @result INT;
   6:  --DECLARE @date1 DATE = '2012-09-24';
   7:  --DECLARE @date2 DATE = '2012-10-12';
   8:   
   9:  SELECT @result = COALESCE(SUM(CASE WHEN WorkDay = 1 THEN 1 ELSE 0 END), 0) FROM mwg.Calendar
  10:  WHERE CalendarDate >= @date1
  11:      AND CalendarDate <= @date2
  12:      
  13:      RETURN @result
  14:  END    
  15:   
  16:  GO



So now I can query a velocity that excludes weekends. I can also toggle off holidays if I choose to.