SQLTeam.com | Weblogs | Forums

How to find working date range between two dates in sql


#1

How to find working date range between two dates in sql. Suppose we take two dates(26-June-2017, 1-July-2017) Then our working date range is (26-June-2017,27-June-2017,28-June-2017,29-June-2017,30-June-2017)


#2

This is normally accomplished using a Calendar table that maintains (among other things) a list of holidays observed by your particular organization, weekends and any other things that would cause a particular date to be closed for business.

Once you have that, it's a simple matter of creating a "WorkingDays" function that accepts begin & end date parameters, that counts the number of "open for business" dates between the two ranges.

Here is a simple example...

CREATE FUNCTION dbo.tfn_WorkingDays 
/* ===========================================
Counts the working days between two dates.
=========================================== */
(
	@BegDate DATETIME, 
	@EndDate DATETIME 
)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN
	SELECT 
		WorkingDays = COUNT(1)
	FROM 
		dbo.Calendar c
	WHERE 
		c.dt >= @BegDate
		AND c.dt <= @EndDate
		AND c.isWeekday = 1
		AND c.isHoliday = 0;
GO 

Then you can simply reference the function as follows...

SELECT 
	*
FROM 
	dbo.MyTable mt
	CROSS APPLY dbo.tfn_WorkingDays(mt.DateBegin, mt.DateComplete) wd;

Keep in mind the function above is just a simple example of this type of function that may or may not suit you needs...
For example, If the begin date is 2017-06-29 23:55:55 and the end date is 2017-06-30 00:00:00, the function will return the value of 1 (clearly not a full day)
AND
If the begin date is 2017-06-29 03:00:00 and the end date is 2017-06-30 22:00:00, the function will return the value of 0 (most people would consider this a full work day)...

Of course, dealing with these issues can necessitate more complex solutions but hopefully you get the basic gist..