CODEWITHSUNDEEP
sqlt-sql
SQLSam
SQLSam

Reputation: 537

Calculating working hours between two dates - Including weekends

I have been given a task to calculate the number of working hours it has taken to ship orders, from when the order was first input to when it shipped. To achieve this, I cannot create any kind of function due to permission issues.

The business hours are Monday - Friday 08:00 - 17:30. Saturday and Sunday - 08:00 - 16:00. It needs to total up the time taken within these core hours.

If the order was placed before the start of the day (8am) then it needs to treat the order as input at 8am. If the order was after the end of the day (5.30pm M-F, 4pm S/SS) then treat as if the order was input at the end of the day. The same is true for deliveries.

I searched this and found previous answers on SO, but found that all of the previously accepted answers use functions to do this.

In my development of this, I have achieved this without the use of functions. I am sharing my answer below for future searches/reference.

Welcome any feedback on how this can be improved upon.

Below is some example order and delivery date to work with.

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL DROP TABLE #Orders;  -- Example data to prove the theory.
    CREATE TABLE #Orders (OrderDate DateTime, DeliveryDate DATETIME)
    INSERT INTO #Orders  (#Orders.OrderDate, #Orders.DeliveryDate) 
    VALUES
       (cast('2020-01-18 13:55:15.000' as datetime), cast('2020-01-19 13:35:56.110' as datetime)), 
       (cast('2020-01-18 23:47:57.000' as datetime), cast('2020-01-19 13:36:40.537' as datetime)), 
       (cast('2020-01-18 07:20:12.000' as datetime), cast('2020-01-19 13:37:41.977' as datetime)), 
       (cast('2020-01-18 08:51:46.813' as datetime), cast('2020-01-19 13:38:35.193' as datetime)), 
       (cast('2020-01-18 12:37:13.000' as datetime), cast('2020-01-19 14:24:35.927' as datetime)), 
       (cast('2020-01-18 12:59:54.000' as datetime), cast('2020-01-19 14:53:23.663' as datetime)), 
       (cast('2020-01-19 13:44:31.000' as datetime), cast('2020-01-19 14:56:47.157' as datetime)), 
       (cast('2020-01-19 13:38:19.000' as datetime), cast('2020-01-19 14:58:09.543' as datetime)), 
       (cast('2020-01-19 08:55:31.050' as datetime), cast('2020-01-20 08:17:25.073' as datetime)), 
       (cast('2020-01-18 21:16:23.000' as datetime), cast('2020-01-20 08:17:52.330' as datetime)), 
       (cast('2020-01-19 08:59:26.650' as datetime), cast('2020-01-20 08:18:05.163' as datetime)), 
       (cast('2020-01-19 08:49:24.193' as datetime), cast('2020-01-20 08:18:49.077' as datetime)), 
       (cast('2020-01-18 15:33:48.000' as datetime), cast('2020-01-20 08:26:24.387' as datetime)), 
       (cast('2020-01-18 18:45:52.000' as datetime), cast('2020-01-20 08:26:29.657' as datetime)), 
       (cast('2020-01-18 20:56:33.000' as datetime), cast('2020-01-20 08:27:25.517' as datetime)), 
       (cast('2020-01-18 08:55:53.100' as datetime), cast('2020-01-20 08:28:25.210' as datetime)), 
       (cast('2020-01-06 00:19:08.000' as datetime), cast('2020-01-20 08:28:27.197' as datetime)), 
       (cast('2020-01-18 17:38:50.000' as datetime), cast('2020-01-20 08:42:16.777' as datetime)), 
       (cast('2020-01-19 14:24:30.000' as datetime), cast('2020-01-20 08:42:37.537' as datetime)), 
       (cast('2020-01-19 12:00:01.000' as datetime), cast('2020-01-20 08:42:53.173' as datetime)), 
       (cast('2020-01-19 13:21:15.000' as datetime), cast('2020-01-20 08:43:18.517' as datetime)), 
       (cast('2020-01-19 04:11:15.000' as datetime), cast('2020-01-20 09:28:34.997' as datetime)), 
       (cast('2020-01-19 09:28:05.000' as datetime), cast('2020-01-20 09:28:51.447' as datetime)), 
       (cast('2020-01-16 22:09:49.000' as datetime), cast('2020-01-20 09:29:23.630' as datetime)), 
       (cast('2020-01-19 13:43:05.000' as datetime), cast('2020-01-20 09:29:41.997' as datetime))

Upvotes: 0

Views: 590

Answers (1)

SQLSam
SQLSam

Reputation: 537

I have commented the SQL as best I can to explain my method in plain English. There is also some dummy data to get this going.

Below is a rough explanation of my method:

Using a CTE - create one row of data for each day that this query will possible ever reference. This can easily be expanded in the future.

Then go through each row returned from the CTE and set the number of working hours for that day, depending on the day of week. Also set the end datetime of that day, again depending on the day of the week. Store this in the DayRows table.

Join in DayRows table where the date is between the order date and the delivery date, and sum up the total working minutes possible from those dates.

Calculate the difference min minutes between the start of the day and the order time. Calculate the difference in minutes between the delivery date and the end of the day.

Subtract these differences off the sum working minutes. This then gives the total number of working minutes between the two dates. Divide by 60 to get back into hours.


---- calculates the number of working hours between order date and delivery date. Working day starts 08:00 each day. Weekdays ends at 17:30. Weekends ends at 16:00

; WITH TempDays AS 
    (SELECT CAST('2015-01-01 08:00:00' AS DATETIME) AS DateValue
    UNION ALL 
    SELECT DATEADD(DAY,1,DateValue) AS DateValue
    FROM TempDays
    WHERE 
       TempDays.DateValue <= '2035-12-31'
    ) -- Recursive CTE to give one row per day between 2015 and 2035


, DayRows as
    ( 
       SELECT
          TempDays.DateValue AS StartDay
          , CASE    
             WHEN datename(WEEKDAY,TempDays.DateValue) IN ('Saturday', 'Sunday')
             THEN DATEADD(HOUR,8,TempDays.DateValue) -- Saturday & sunday
             ELSE DATEADD(MINUTE,30,DATEADD(HOUR,9,TempDays.DateValue)) -- Weekday
             END AS EndDay
          , CASE When datename(WEEKDAY,TempDays.DateValue) IN ('Saturday', 'Sunday')
             Then 480 -- Saturday & Sunday 8 hours
             ELSE 570 -- Weekday  9.5 hours
             END AS WorkMinutes
       FROM 
          TempDays 
    ) -- This calcualtes the Start and End datetime for each day returned. If Weekend, end time is 16:00. Weekday is 17:30. All days start at 8:00



SELECT
    #Orders.Orderdate
    , #Orders.DeliveryDate
    , WorkingHours.WorkMins
    , StartHours.MinutesDayStart
    , EndHours.MinutesDayEnd
    , CAST( ((WorkingHours.WorkMins - ISNULL(StartHours.MinutesDayStart,0) - ISNULL(EndHours.MinutesDayEnd,0))  / 60.00) AS DECIMAL(32,2)) AS Working_Hours_To_Ship -- Takes total working minutes for all the days inbetween order and delivery, then removes the number of mins between start of day and order and the delivery and end of the day.

FROM 
    #Orders
    OUTER APPLY
       (
          SELECT
             SUM(DayRows.WorkMinutes)  AS WorkMins 
          FROM 
             DayRows
          WHERE 
            CAST(DayRows.StartDay AS DATE) >= CAST(#Orders.Orderdate AS DATE) AND CAST(DayRows.EndDay AS DATE) <= CAST(#Orders.DeliveryDate AS DATE) 
       ) AS WorkingHours -- Calculates the sum total of working hours for all days between the order date and delivery date, including the order and del date.


    OUTER APPLY
       (SELECT
          DATEDIFF(MINUTE,DayRows.StartDay,
                CASE 
                    WHEN CAST(#Orders.Orderdate AS TIME) < cast(DayRows.StartDay AS TIME)
                    THEN DayRows.StartDay
                    ELSE 
                       CASE 
                          WHEN CAST(#Orders.Orderdate AS TIME) > cast(DayRows.EndDay AS TIME)
                          THEN DayRows.EndDay
                          Else #Orders.Orderdate
                          End
                    End
                ) AS MinutesDayStart
       FROM 
          DayRows
       WHERE 
         CAST(DayRows.StartDay AS DATE) = CAST(#Orders.Orderdate AS DATE) 
       ) AS StartHours -- Calcualtes the number of minutes between the start of the day and the order date. This is then to be deducted off the total working hours.

    OUTER APPLY
       (SELECT
          DATEDIFF(MINUTE,
             CASE 
                WHEN CAST(#Orders.DeliveryDate AS TIME) < cast(DayRows.StartDay AS TIME) -- If the delivery was made before the start of the day, then uses start of day as delivery time.
                THEN DayRows.StartDay
                ELSE 
                    CASE 
                       WHEN CAST(#Orders.DeliveryDate AS TIME) > cast(DayRows.EndDay AS TIME)  -- If the delivery was made after the end of the day, then uses the end of the day as the delivery time
                       THEN DayRows.EndDay
                       Else #Orders.DeliveryDate
                    End
             END,      
             DayRows.EndDay
          )  AS MinutesDayEnd 
       FROM 
          DayRows
       WHERE 
         CAST(DayRows.StartDay AS DATE) = CAST(#Orders.DeliveryDate AS DATE) 
       ) AS EndHours -- Calculates the number of minutes between the delivery date and the end of the day.


WHERE 
    #Orders.Orderdate >= '2020-01-01'

option (maxrecursion 0)

Upvotes: 2

Related Questions