question

ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked ·

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

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

sql-server-generalsql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

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

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @ArunChandramouli-6978,

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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

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 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ·

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/

·
10 |1000 characters needed characters left characters exceeded

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