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.