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:
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.
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 |