Home / SQL Server / Calculate DateDiff With Only WeekDays or Workdays

Calculate DateDiff With Only WeekDays or Workdays

Today, I was working on a fact table for my datawarehouse. I was looking for a function to calculate the number of “normal weekdays” between two given dates  I had a record in my Dwh from a HR system with two dates (start-end) and a number of hours “sick” .  For example, a person was sick between 1th January 2012 till 28th January 2012 with a number of hours of 160.  There was a need to divide this number of hours over more dates to get sickness hours by date. So 20120102: 8 hours sick, 20120103 8 hours sick, etc.  I started looking on Google for such a function and I found one. I also created a more flexible one but you need a Date Dimension for this.

This function will calculate the number of working days / weekdays (monday till friday) between two dates. It works the same as the datediff function but skips the weekends (saturday – sunday). I works easy, just add this little piece of code in your SELECT statement:

SELECT dbo.DateDiffWorkDays('2012-01-07','2012-01-14')

Download this function below:

 DateDiffWorkDays.sql

The second method is better. This one uses my date dimension. I assume you also use one in your datamart 🙂 I convert the datetime to an int to get proper results.

Click here for an example.

Please configure this function on your needs.

 

CREATE FUNCTION [dbo].[DateDiffWorkDays]
( @StartDate smalldatetime, @EndDate smalldatetime, @IncludeHolidays BIT = 0 )
 
RETURNS INT
 
AS
 
BEGIN
 
DECLARE @StartDateID INT
SET @StartDateID = CONVERT(VARCHAR,@StartDate,112)
DECLARE @EndDateID INT
SET @EndDateID = CONVERT(VARCHAR,@EndDate,112)
 
DECLARE @NrOfDays INT
 
SELECT @NrOfDays = COUNT(*)
FROM dbo.Datum AS DD
WHERE DD.DatumID BETWEEN @StartDateID AND @EndDateID
AND IsWerkDag= 1
AND ( @IncludeHolidays = 1
OR IsFeestDag_NL = 0)
 
RETURN ( @NrOfDays )
END
 

Check Also

Nederlandse postcodetabel

Postcodetabel van Nederlands + SQL Script

Inleiding De Nederlandse Postcodetabel bevat informatie over alle postcodes die in Nederland bekend zijn. Dit …

Geef een reactie

Je e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *