Finding the difference between datetimes excluding sundays and Business holidays

1

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

2

Btw, there is NO U.S. fed holiday called "Presidents Day", the actual holiday is "George Washington's Birthday" :grinning:

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number 
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT 
    i.ctextid, i.vbillid,
    --assigndate, t.number,
    --workdate,
    SUM(
        CASE WHEN t.number = 0
            THEN DATEDIFF(MINUTE, i.assigndate, DATEADD(DAY, 1, workdate))
            WHEN ca1.workdate = CAST(i.completed AS date)
            THEN DATEDIFF(MINUTE, CAST(i.completed AS date), i.completed)
            ELSE 1440
        END
    ) / 60.0 AS total_hours,
    COUNT(DISTINCT i.assigndate) AS total_holds
FROM ##input i
INNER JOIN cte_tally1000 t ON
    t.number BETWEEN 0 AND DATEDIFF(DAY, i.assigndate, i.completed) - 1
CROSS APPLY (
    SELECT DATEADD(DAY, t.number, CAST(i.assigndate AS date)) AS workdate
) AS ca1
WHERE
    DATEDIFF(DAY, 6, workdate) % 6 <> 0 AND
    NOT EXISTS(
        SELECT 1
        FROM ##holidays
        WHERE holidaydate = workdate
    )
GROUP BY ctextid, vbillid
ORDER BY 1, 2, 3