title: Calculating working hours between two dates - Including weekends
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>I searched this and found previous answers on SO, but found that all of the previously accepted answers use functions to do this.</p>
  
  <p>In my development of this, I have achieved this without the use of functions. I am sharing my answer below for future searches/reference.</p>
  
  <p>Welcome any feedback on how this can be improved upon.</p>
  
  <p>Below is some example order and delivery date to work with.</p>
  
  <pre><code>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))
  
  </code></pre>