3 Replies Latest reply: Jan 5, 2018 5:10 PM by Justin Dallas RSS

    Count late delivery type Stop only if pickup Stop was On-Time

    Justin Dallas

      Hello Everyone,

       

      I have a problem where I am attempting to answer the following question:

       

      "Count the late delivery stops, ONLY when the associated pickup stop was on-time"

       

      For instance, here is a sample script:

       

      Stops:
      LOAD * Inline
      [
      'OrderNumber', 'StopNumber', 'Action', 'WasLate'
          1, 101, Pickup, Y
          1, 102, Delivery, N
          1, 103, Delivery, Y
         
          2, 201, Pickup, N
          2, 202, Delivery, Y
          2, 203, Delivery, Y
         
          3, 301, Pickup, N
          3, 302, Delivery, Y
          3, 303, Delivery, N
      ]
      
      

       

       

      In this instance, a following Table would have the following results.

       

      Order Number | Late Stops

      1     ,     0

      2     ,     2

      3     ,      1

       

      The reason the OrderNumber "1" has a LateStop count of 0 is because the Pickup was late, so we don't count those Late Deliveries (Stop Number=103).

       

      In SQL I would say something like this:

      SELECT COUNT(*) FROM Stops s
      WHERE s.WasLate = 'Y'
      AND s.OrderNumber NOT IN
      (
          SELECT OrderNumber FROM Stops late
          WHERE late.Action = 'Pickup'
          AND late.WasLate = 'Y'
      )
      
      

       

       

      Any help on this is greatly appreciated.