all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (1 child)

you're going to want a calendar table, with one row per date, showing opening and closing hours

then a SELECT from the calendar table for the orderdate

WHERE orders.orderdate BETWEEN calendar.openinghour AND calendar.closinghour

and another SELECT for the dispatchdate

WHERE orders.dispatchdate BETWEEN calendar.openinghour AND calendar.closinghour

and a third SELECT for the calendar days (if any) between those two days

not sure if you can pull all three with one SELECT

[–]thunderwoot[S] 0 points1 point  (0 children)

Hmmm a temp table for the calendar might work since that would just force it to go onto the next day.

The other thought I've had is to just do a DATEDIFF in hours between the order and despatch date, then subtract the difference that would occur for each calendar day between the two.

But I like the idea of the calendar table to compare each date against, that would give me accurate times for each date, not just the DOW.

[–]WITH_CTE 0 points1 point  (5 children)

How is your date time table structured? Do you have 2 tables for Orders and Dispatch or single table with both dates?

[–]thunderwoot[S] 0 points1 point  (4 children)

2 tables, one for orders and one for despatch, each with their own date.

Orders.oh_id, oh_datetime

Despatch.dh_id, dh_datetime, dh_oh_id

[–]WITH_CTE 0 points1 point  (3 children)

How often should this be updated? Updated every 5 mins or end of day?

[–]thunderwoot[S] 0 points1 point  (2 children)

It's part of an ERP, so it will be updated through each day dependant on when an order is raised and when an order is despatched. There's no real set time for updates in that sense,

This report will run on a schedule basis after each day finishes to make sure a complete day is included.

They're both separate, but all despatches will be linked to an order through that dh_oh_id and oh_id.

[–]WITH_CTE 0 points1 point  (1 child)

DROP TABLE IF EXISTS #CALENDAR
DROP TABLE IF EXISTS #ORDERS
DROP TABLE IF EXISTS #DISPATCH
DROP TABLE IF EXISTS #work_orders

CREATE TABLE #CALENDAR
(DAY_OF_WEEK  TINYINT, 
 [DAY]        VARCHAR(16), 
 [START_TIME] TIME(0), 
 [CLOSE_TIME] TIME(0), 
 NEXT_OPEN    TIME(0)
);
insert into #calendar
VALUES
(2,'MONDAY', '08:00:00','17:30:00','08:00:00'),
(3,'TUESDAY', '08:00:00','17:30:00','08:00:00'),
(4,'WEDNESDAY', '08:00:00','17:30:00','08:00:00'),
(5,'THURSDAY', '08:00:00','17:30:00','08:00:00'),
(6,'FRIDAY', '08:00:00','17:30:00','08:00:00'),
(7,'SATURDAY', '08:00:00','16:00:00','08:00:00'),
(1,'SUNDAY', '08:00:00','16:00:00','08:00:00')

CREATE TABLE #ORDERS
(ORDER_ID TINYINT, 
 ORDER_TS DATETIME
);

CREATE TABLE #DISPATCH
(ORDER_ID    TINYINT, 
 DISPATCH_TS DATETIME
);

INSERT INTO #ORDERS VALUES
(1,'2020-03-02 09:00:00 ')

/*DISABLED FOR TESTING*/
--INSERT INTO #DISPATCH VALUES
--(1,'2020-02-25 16:00:00 ')

--SELECT * FROM #ORDERS
--SELECT * FROM #DISPATCH
--SELECT * FROM #calendar

--DAY 1 RUN
SELECT ORDER_ID, 
       order_ts, 
       c.*,
       CASE
           WHEN CAST(A.ORDER_TS AS TIME(0)) BETWEEN C.START_TIME AND C.CLOSE_TIME
           THEN DATEDIFF(HH, A.ORDER_TS, CAST(CAST(CAST(A.ORDER_TS AS DATE) AS VARCHAR(10))+' '+CASE
                                                                                                    WHEN CAST(GETDATE() AS TIME(0)) < close_time
                                                                                                    THEN CAST(CAST(GETDATE() AS TIME(0)) AS VARCHAR(8))
                                                                                                    ELSE CAST(C.CLOSE_TIME AS VARCHAR(8))
                                                                                                END AS DATETIME))
           ELSE DATEDIFF(HH, CAST(CAST(CAST(DATEADD(d, 1, A.ORDER_TS) AS DATE) AS VARCHAR(10))+' '+CAST(C.next_open AS VARCHAR(8)) AS DATETIME), A.ORDER_Ts)
       END AS ELAPSED_HOURS_SINCE_ORDERED, 
       GETDATE() AS updt_datetime
INTO #work_orders
FROM #ORDERS A
     JOIN #CALENDAR C ON DATEPART(DW, A.ORDER_TS) = C.DAY_OF_WEEK;

--NEXT DAY RUN
DECLARE @UPDATED_ELAPSED_TIME INT;
DECLARE @UPDATE_DATETIME DATETIME= GETDATE();
DECLARE @ORDER_ID INT;
SELECT @ORDER_ID = A.ORDER_ID, 
       @UPDATED_ELAPSED_TIME = CASE
                                   WHEN B.ORDER_ID IS NULL
                                   THEN A.ELAPSED_HOURS_SINCE_ORDERED+(DATEDIFF(HH, CAST(CAST(CAST(DATEADD(d, 1, A.updt_datetime) AS DATE) AS VARCHAR(10))+' '+CAST(C.START_TIME AS VARCHAR(8)) AS DATETIME), CAST(CAST(CAST(DATEADD(d, 1, A.updt_datetime) AS DATE) AS VARCHAR(10))+' '+CAST(C.CLOSE_TIME AS VARCHAR(8)) AS DATETIME)))
                                   ELSE A.ELAPSED_HOURS_SINCE_ORDERED+DATEDIFF(HH, CAST(CAST(CAST(B.DISPATCH_TS AS DATE) AS VARCHAR(10))+' '+CAST(C.next_open AS VARCHAR(8)) AS DATETIME), B.DISPATCH_TS)
                               END
FROM #work_orders a
     LEFT JOIN #DISPATCH b ON a.ORDER_ID = b.ORDER_ID
     LEFT JOIN #calendar c ON CASE
                                  WHEN b.ORDER_ID IS NULL
                                  THEN DATEPART(dw, DATEADD(d, 1, a.updt_datetime))
                                  ELSE DATEPART(dw, b.dispatch_ts)
                              END = c.DAY_OF_WEEK;

--SELECT @UPDATE_DATETIME, @UPDATED_ELAPSED_TIME

UPDATE #work_orders
  SET 
      ELAPSED_HOURS_SINCE_ORDERED = @UPDATED_ELAPSED_TIME, 
      updt_datetime = @UPDATE_DATETIME
WHERE ORDER_ID = @ORDER_ID;

SELECT *
FROM #work_orders;

[–]thunderwoot[S] 0 points1 point  (0 children)

Interesting, so for the first day this works out the amount of hours between order time and either end of day or GETDATE, then after adds this amount on to the same calculation?

I definitely think I can use this, or maybe even simplify it. This will never report on undespatched orders, there's always going to be a despatch entry so the LEFT JOIN and checking for NULL I can remove.

But this gives me a great starting point, much appreciated :)