0 Replies Latest reply: Apr 14, 2014 4:42 AM by Jean Jordaan

# Check deliveries are in a sequence.

1. 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. 1. If the planned delivery number is 1st in the Stop Sequence AND number 1 in the Actual Delivery sequence.
2. 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