Could someone please solve a query in SQL that calculate working hours between two dates which working hours are:
SAT – THU —> Working Hours is from 7AM to 11PM
Fri —> Working Hours is from 1PM to 11PM
Example 1:
Inputs:
Date 1 = Saturday 22-06-2024 09:30 PM
Date 2 = Monday 24-06-2024 08:00 AM
Output: 18.5 hours
Example 2:
Inputs:
Date 1 = Saturday 22-06-2024 09:30 PM
Date 2 = Sunday 23-06-2024 08:00 AM
Output: 2.5 hours
Because the time between 11PM to 7AM is dead for Saturday to Thursday.
I’ve tried to reach the solution but it’s difficult for me.
Anwar Algawzi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
DECLARE @Date1 DATETIME = '2024-06-22 21:30'; -- Sat 9:30 PM
DECLARE @Date2 DATETIME = '2024-06-24 08:00'; -- Monday 8:00 AM
-- Determine if a datetime is within working hours
CREATE FUNCTION dbo.IsWorkingHour(@datetime DATETIME)
RETURNS BIT
AS
BEGIN
DECLARE @result BIT = 0;
DECLARE @dayOfWeek INT = DATEPART(WEEKDAY, @datetime);
DECLARE @hour INT = DATEPART(HOUR, @datetime);
IF (@dayOfWeek BETWEEN 2 AND 7 AND @hour BETWEEN 7 AND 22) -- SAT to THU, 7AM to 11PM
OR (@dayOfWeek = 1 AND @hour BETWEEN 13 AND 22) -- FRI, 1PM to 11PM
BEGIN
SET @result = 1;
END
RETURN @result;
END;
-- Calculate total working hours
WITH DateSeries AS (
SELECT @Date1 AS dt
UNION ALL
SELECT DATEADD(MINUTE, 1, dt)
FROM DateSeries
WHERE dt < @Date2
),
WorkingHours AS (
SELECT dt
FROM DateSeries
WHERE dbo.IsWorkingHour(dt) = 1
)
SELECT COUNT(*) / 60.0 AS WorkingHours
FROM WorkingHours
OPTION (MAXRECURSION 0);
This solution works for SQL Server. This would give you the accurate working hours between any two given dates according to the specified working hours.