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

    Check deliveries are in a sequence.

    Jean Jordaan
      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