Calculate working hours between two dates based on business hours-CodePudding

I have a table in SQL Server representing service requests with their created date and LastUpdateAt which represents the end date if exist, if null it means the request is open and not yet solved.

I want to calculate the working hours for each request to be closed or if it's not yet closed, then the hours from the created date until current time, with weekends excluded and only calculating working hours each working day, from 8am to 4pm "8 hours per working day".

What I have tried so far:

WITH CTE AS
( 
    SELECT
        Id,
        CreatedAt AS DYN_DATE,
        CreatedAt AS START_DATE,
        (CASE 
             WHEN LastUpdateAt IS NULL 
                 THEN GETDATE() 
                 ELSE LastUpdateAt 
         END) AS END_DATE
    FROM 
        Request
    WHERE 
        Id IN ('14578')
)
SELECT
    (DATEDIFF(HOUR, START_DATE, END_DATE) - (DATEDIFF(DAY, START_DATE, END_DATE) * 16 )) - 
    ((SELECT COUNT(*) FROM CTE 
      WHERE DYN_DATE BETWEEN START_DATE AND END_DATE 
        AND DATEPART(WEEKDAY, DYN_DATE) NOT IN (6, 7)) * 24) AS B_DURATION
FROM
    CTE

In this query I am trying to calculate the working hours for the request ID : 14578.

In the last part of the query, I was trying to calculate the total hours then subtract the number of non-working hours then also subtract the number of (weekends * 24).

Probably my method is wrong from the beginning, or I am thinking inside the box, so I am open to any suggestion or solution.

CodePudding user response：

There are multiple phases to this calculation. It may be helpful to look at a calendar for the explanation.

Weeks between start and end

DATEDIFF(WEEK, cte2.START_DATE, cte2.END_DATE) As NumWeeks

This is calculating the difference between the rows of a calendar, a week. (a 7 day period generally represented as a single row on a calendar). Saturday and the following Sunday may only be one day apart, but they are in separate weeks, and therefore are in separate rows on the calendar.

Week Days between start and end.

DATEPART(WEEKDAY, cte2.END_DATE) - DATEPART(WEEKDAY, cte2.START_DATE) as NumDays

Step 1 calculated the difference in rows of the calendar, step 2 we're calculating the difference in columns. This will account for partial week differences.

Tuesday to the following Monday is six days, one day less than a week. Step one returned 1 week. Since we're shifting one column to the left, we adjusting the week by -1 days. If the end date had been Wednesday, it would be 1 week plus 1 day, but since it is Monday, it is 1 week minus 1 day.

Normalizing the Start/End time outside of working hours

CAST(CASE WHEN CAST(CTE.START_DATE AS TIME) < '08:00' THEN '08:00'
     WHEN CAST(CTE.START_DATE AS TIME) > '16:00' THEN '16:00'
     ELSE CAST(CTE.START_DATE AS TIME)
     END AS DATETIME) as StartTimeOnly,
CAST(CASE WHEN CAST(CTE.END_DATE AS TIME) < '08:00' THEN '08:00'
     WHEN CAST(CTE.END_DATE AS TIME) > '16:00' THEN '16:00'
     ELSE CAST(CTE.END_DATE AS TIME)
     END AS DATETIME) as EndTimeOnly

This calculation is only interested in the time, so we cast to Time, then back to DateTime. This sets the Date component for both to 1900-01-01.

Similar to the week & day relationship, an End Time that occurs the next day, but before the Start Time will subtract hours credited from the number of days. For example 1/2 at 12:00 to 1/3 at 10:00 would be 1 day(1/3 - 1/2), or 8 hours, - 2 hours (10-12) = 6 hours of business time.

Calculating the difference in minutes, to ensure partial hours are considered correctly, then converting to hours. This ensures times only a couple minutes apart across an hour boundary don't get counted as a full hour. Of course, the trade off is 59 minutes rounds down to 0 hours.

DATEDIFF(MINUTE, StartTimeOnly, EndTimeOnly)/60 as NumHours

If rounding at the half hour...

CAST(ROUND(DATEDIFF(MINUTE, StartTimeOnly, EndTimeOnly) / 60.0, 0) AS INT) AS RoundedHours

Wrap it all together

,
  cte2 AS
  (
    SELECT CTE.START_DATE,
           CTE.END_DATE,
           StartTimeOnly = CAST(CASE WHEN CAST(CTE.START_DATE AS TIME) < '08:00' THEN '08:00'
                                WHEN CAST(CTE.START_DATE AS TIME) > '16:00' THEN '16:00'
                                ELSE CAST(CTE.START_DATE AS TIME)
                                END AS DATETIME),
           EndTimeOnly   = CAST(CASE WHEN CAST(CTE.END_DATE AS TIME) < '08:00' THEN '08:00'
                                WHEN CAST(CTE.END_DATE AS TIME) > '16:00' THEN '16:00'
                                ELSE CAST(CTE.END_DATE AS TIME)
                                END AS DATETIME)
    FROM   CTE
  ),
  CTE3 AS
  (
    SELECT START_DATE = CAST(cte2.START_DATE AS DATETIME2(0)),
           END_DATE   = CAST(cte2.END_DATE AS DATETIME2(0)),
           NumWeeks   = DATEDIFF(WEEK, cte2.START_DATE, cte2.END_DATE),
           NumDays    = DATEPART(WEEKDAY, cte2.END_DATE) - DATEPART(WEEKDAY, cte2.START_DATE),
           NumHours = DATEDIFF(MINUTE, cte2.StartTimeOnly, cte2.EndTimeOnly)/60
    FROM   cte2
  )
SELECT CTE3.START_DATE,
       CTE3.END_DATE,
       CTE3.NumWeeks,
       CTE3.NumDays,
       CTE3.NumHours,
       TotalBusinessHours = (CTE3.NumWeeks * 5 * 8)   (CTE3.NumDays * 8)   (CTE3.NumHours )
FROM   CTE3
;

For more accurate results, you'll also want to add a table containing your holidays. You'll then subtract the number of holidays found between your start and end date from your total number of days, before converting it to hours.

A question you may still need to answer... what happens if the start and end dates occur during non-working hours? e.g. Start at 19:00 and finish at 20:00. Is that 0 business hours to resolve?