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