Finding the difference between datetimes excluding sundays and Business holidays

Question

Hi All,

Hope you are doing well!... I am trying to find the difference in the date times between the assign datetime and the completed datetime for a task completion (in hours and minutes) identified by the combination of ctextid and vbillid ..Sometimes a task involves multiple attempts and the number of times the combination of ctextid and vbillid appears is the totalholds.Also the business holiday dates in the holidays table should be excluded in the hours:minutes calculations plus the sundays should be excluded in hours:minutes calculations ( meaning sundays and business holidays should be excluded between and also inclusive of the assign and completed dates )

1) If the assigned dates and the completed dates fall on US Business holidays or on Sundays those hours are not taken into account for total hours
2) If the assign date starts with a sunday or business holiday those hours are not taken into account and also if the completed date falls on a sunday or business holiday those hours are not taken into account.
3) Also if the times between the assigned dates and the completed date fall on Sundays or Business holidays then those hours are not taken into account
4) Also if the assigned and the completed fall on the same day and it is a Sunday or Business holiday those hours are considered zero for that hold

Please find the tables below:Can you please help me here

Input table

create table ##input
(ctextid int,
vbillid int,
assigndate datetime2,
completed datetime2
)

insert into ##input values
('23','11','1/25/2021 6:51','2/9/2021 16:55'),
('23','11','2/10/2021 6:51','2/12/2021 6:51'),
('34','16','1/7/2021 18:58','2/24/2021 9:00'),
('44','102','1/22/2021 9:56','2/9/2021 16:54),
('44','91','1/19/2021 16:35','1/20/2021 4:14'),
('75','57','1/19/2021 10:11','1/19/2021 13:16')

Holidays table

create table ##holidays
(description varchar (500),
holidaydate date
)

insert into ##holidays values
('New Year's Day','1/1/2021'),
('Martin Luther King's Birthday','1/18/2021'),
('President's Day','2/15/2021'),
('Memorial Day','5/31/2021'),
('Independence Day','7/5/2021'),
('Labor Day','9/6/2021'),
('Columbus Day','10/11/2021'),
('Veterans Day','11/11/2021'),
('Thanksgiving Day','11/25/2021'),
('Christmas Day','12/24/2021'),
('New Year's Day','12/31/2021')

Output table

create table ##output
(ctextid int,
vbillid int,
hoursintotal float,
totalholds int
)
insert into ##output values
('23','11','370.04','2'),
('34','16','926.02','1'),
('44','102','366.57','1'),
('44','91','11.38','1'),
('75','57','3.04','1')

Thanks,
Arun

Answer 1

Hi @Hellothere8028 ,

Welcome to Microsoft Q&A!

Please refer below:

Step One: create one function named [fn_GetTotalWorkingHours] to calculate the working hours exclude Sunday.

CREATE FUNCTION [dbo].[fn_GetTotalWorkingHours]  
(  
    @DateFrom Datetime,  
    @DateTo Datetime  
)  
RETURNS DECIMAL(18,2)  
AS  
BEGIN  
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)  
SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)  
				    -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 1)  
					   -CASE  
                                    WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'  
                                    THEN 1  
                                    ELSE 0  
                                END+CASE  
                                        WHEN DATENAME(WEEKDAY, @DateTo) = 'Sunday'  
                                        THEN 1  
                                        ELSE 0  
                                    END;  
SET @TotalTimeDiff =  
(  
    SELECT DATEDIFF(MINUTE,  
                   (  
                       SELECT CONVERT(TIME, @DateFrom)  
                   ),  
                   (  
                       SELECT CONVERT(TIME, @DateTo)  
                   )) / 60.0  
);  
RETURN  (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)  
END  
GO  

Step Two: Call this function in below query:

;WITH cte1 as (  
SELECT a.ctextid,a.vbillid  
,case when CONVERT(date, DATEADD(DAY,number,a.assigndate))=CONVERT(date, a.completed)    
then  CONVERT(date, a.completed) else DATEADD(DAY,number,CONVERT(date, a.assigndate)) end [ALLDate]  
FROM master..spt_values,##INPUT a  
WHERE type = 'P'  
AND DATEADD(DAY,number,CONVERT(date, a.assigndate)) <= CONVERT(date, a.completed))  
,cte2 as   
(select a.*   
,case when DATENAME(DW,[ALLDate])='Sunday' then 1 else 0 end IsSunday  
,IIF(b.holidaydate is not null,1,0) IsHoliday  
from cte1 a  
left join ##holidays b  
on ALLDate=CONVERT(date, b.holidaydate ))  
,CTE3 AS   
(select ctextid,vbillid,SUM(IsHoliday)  SUMHOLIADY  
from cte2  
WHERE IsHoliday=1 AND IsSunday=0  
GROUP BY ctextid,vbillid)  
,CTE4 AS   
(select DISTINCT A.ctextid,A.vbillid  
,[dbo].[fn_GetTotalWorkingHours] (assigndate,completed) hours   
from ##input A)  
,CTE5 AS (  
select A.ctextid,A.vbillid,sum(hours) sum, count(hours) count  
from CTE4 A  
group by A.ctextid,A.vbillid)  
SELECT A.ctextid,A.vbillid  
,A.SUM-(ISNULL(B.SUMHOLIADY,0)*24) hoursintotal ,COUNT totalholds    
FROM CTE5 A  
LEFT JOIN CTE3 B   
ON A.ctextid=B.ctextid AND A.vbillid=B.vbillid  

Output:

ctextid	vbillid	hoursintotal	totalholds  
23	11	370.07	2  
34	16	926.03	1  
75	57	3.08	1  
44	91	11.65	1  
44	102	366.97	1  

After checking, the hoursintotal from my output is a little different from your's.

I checked manually with one row ('75','57','1/19/2021 10:11','1/19/2021 13:16') and calculated the hours manually. The minutes between the two dates was 185 and the hoursintotal could be 185/60.00=3.08 which was not 3.04 you provided.

Please check above and provide more sample data and expected output if you have any concern or my query is not working.

Thank you for understanding!

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Gert yourself a full-fledged calendar table with one row per date and with flags to hold whether the day is a business day. With such a table, this query is a breeze.

Here is a good article from Ed Pollack on how to design such a table: https://www.sqlshack.com/designing-a-calendar-table/