- Hi.
I need to calculate if a delivery truck follows the planned delivery route. Below is an example of the data. In the sample data we have the following fields.
[Shipment No], - This is the number for all the deliveries on the truck. A single shipment will have many Deliveries Numbers.
[Stop Sequence], - This is the planned sequence for each shipment.
[Delivery No], - Each delivery number is unique within a shipment.
[Customer Arrival Date],
[Customer Arrival Time] - This is the actual delivery time. From this it is possible to derive the actual delivery sequence.
The requirement is to see if the actual delivery sequence matches up with the planned delivery sequence. A delivery is seen as “in sequence” when the following conditions are true.
- 1. If the planned delivery number is 1st in the Stop Sequence AND number 1 in the Actual Delivery sequence.
- 2. If the previous delivery number in the actual deliver sequence is equal to the Previous Planned Delivery number.
Here is an example of how it should be .
Some of the stops are at malls. So the stop sequence for a couple of deliveries are the same but with different delivery numbers. However when calculating the actual based on the Customer delivery time the times are not always the same for that one stop. I have been told that I can use the MIN time as the customer Delivery time for that site.
In eed help in checking if they are in sequence or not.
Shipment Number | Delivery Number | Stop Sequence | Customer Delivery Time | actual Sequence | In Sequense | Notes |
SH001 | DEL001 | 1 | | 1 | Yes | Actual Sequence 1 and Planned is 1 |
SH001 | DEL002 | 1 | | 1 | Yes | Actual Sequence 1 and Planned is 1 |
SH001 | DEL003 | 1 | | 1 | Yes | Actual Sequence 1 and Planned is 1 |
SH001 | DEL004 | 2 | | 12 | No | The previous planned delivery number for the actual delivery number is not matching. The Planned previsous delivery number (11) is DEL18, However the Actual previos delivery number for this one is (DEL001 and DEL003 and DEL002) |
SH001 | DEL005 | 3 | | 3 | NO | Even though the actual and the planned sequence is the same, we are interested in the if the sequence between the actual previsou delivery number and the planned previsou is the same. This is Actual number 3 in the sequence. The Planned previous based on the planned current is DEL004, The Actual Sequence (2) delivery number is DEL009. |
SH001 | DEL006 | 4 | | 4 | Yes | |
SH001 | DEL007 | 5 | | 5 | Yes | |
SH001 | DEL008 | 6 | | 6 | Yes | |
SH001 | DEL009 | 7 | | 2 | No | |
SH001 | DEL010 | 8 | | 8 | No | |
SH001 | DEL011 | 8 | | 8 | No | |
SH001 | DEL012 | 8 | | 8 | No | |
SH001 | DEL013 | 8 | | 8 | No | |
SH001 | DEL014 | 8 | | 8 | No | |
SH001 | DEL015 | 9 | | 9 | Yes | |
SH001 | DEL016 | 10 | | 10 | Yes | |
SH001 | DEL017 | 10 | | 10 | Yes | |
SH001 | DEL018 | 11 | | 11 | Yes | |
SH001 | DEL019 | 12 | | 7 | No | |
