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.