Calculate Number of Business Days / Working Days between 2 given dates using Date Dimension

This article demonstrates ways to calculate the number of business days or working days between 2 given dates. Business days’ calculation is depending on country or sometime reign or may be specific for a company. If you need to ignore holiday then you don’t need to follow this approach, you can use SQL default functions.

Follow the below steps:

Step 1: Introduce 2 flag in Date dimensions as shown below:

2. Crete a function as shown below

Note: In this function, date key (primary key of date dimension) is used instead of date. The reason is to get best performance out of this function when we run on large query. Primary key would use index seek instead of index scan in where condition in a query.

3. Now you can use this function in a query, even in a huge data set this would give good performance. However, using function against large data set is not recommended practice.

SELECT dbo.getNoOfWokingDays('2016-10-10','2016-10-13')

Calculating weekdays

If you need to calculate number of weekdays, you can use SQL techniques, I mean without considering holidays.

Note: You have to modify the above script before use this code according to your requirements.

Code:

/***Calculate Business Days / Working Days ****/

CREATE FUNCTION dbo.getNoOfWokingDays(@startDate date, @endDate date)

RETURNS int

AS   

BEGIN  

DECLARE @startDateKey int = CONVERT( INT, CONVERT(VARCHAR(8), @startDate, 112))

DECLARE @endDateKey int = CONVERT( INT, CONVERT(VARCHAR(8), @endDate, 112))

DECLARE @days int;  

SELECT @days=(SUM(CASE WHEN [WeekEndFlag] = 'N' AND [HolidayFlag] = 'N' THEN 1 ELSE 0 END))

FROM [dbo].[DimDate]

WHERE [DateKey]>= @startDateKey AND [DateKey]<@endDateKey;

IF (@days IS NULL OR @startDateKey=@endDateKey)   

SET @days = 0;  

RETURN @days;  

END;

You can test the function as shown below:

SELECT dbo.getNoOfWokingDays('2016-10-10','2016-10-13')

/***Calculate Weekdays ****/

DECLARE @startDate date, @endDate date

SET @startDate='2016-10-10'

SET @endDate = '2016-10-13'

SELECT

DATEDIFF(day, @startDate, @endDate) AS [No of Days]

,DATEDIFF(dd, @startDate, @endDate)

- (DATEDIFF(wk, @startDate, @endDate) * 2) -

CASE

WHEN DATEPART(dw, @startDate) = 1 THEN 1 ELSE 0 END +

CASE

WHEN DATEPART(dw, @endDate) = 1 THEN 1 ELSE 0 END AS [No of Working Days]

Cheers!
Uma