question

Finding the difference between datetimes excluding sundays and Business holidays

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

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

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Welcome to Microsoft Q&A!

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

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.

· 1 ·

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Melissa!...This is awesome!!..Thank you so much!

0 Votes 0 ·
1 Vote"