1 Reply Latest reply: Jun 24, 2013 11:04 AM by Ashley Arthur RSS

    Days between Orders

      I am trying to calculate the number of days since the previous order for a given customer and item combination when loading an order file in the load script.  For example, I have an order file that looks like this:

       

      OrderNumber, OrderDate, Customer, Item:

      Order123, 01/01/2013, ABC, ItemA,

      Order129, 01/26/2013, ABC, ItemA,

      Order130, 02/02/2013, ABC, ItemA,

      Order124, 01/01/2013, ABC, ItemB,

      Order128, 01/15/2013, ABC, ItemB,

      Order131, 02/16/2013, ABC, ItemB,

      Order125, 01/01/2013, ABC2, ItemA,

      Order126, 01/05/2013, ABC2, ItemA,

      Order127, 01/15/2013, ABC2, ItemA

       

      And, what I want to end up with is a file that looks like this after the load script is executed:

       

      OrderNumber, OrderDate, Customer, Item, DaysSincePreviousOrder:

      Order123, 01/01/2013, ABC, ItemA, 0

      Order129, 01/26/2013, ABC, ItemA, 25

      Order130, 02/02/2013, ABC, ItemA, 7

      Order124, 01/01/2013, ABC, ItemB, 0

      Order128, 01/15/2013, ABC, ItemB, 14

      Order131, 02/16/2013, ABC, ItemB, 31

      Order125, 01/01/2013, DEF, ItemA, 0

      Order126, 01/05/2013, DEF, ItemA, 4

      Order127, 01/15/2013, DEF, ItemA, 10

       

      Any suggestions would be greatly appreciated!

        • Re: Days between Orders

          Please excuse the inconsistency in my customer numbers from the previous post.  Customer ABC2 should have been DEF in the original order file.  Here is a more consistent data set:

           

          OrderNumber, OrderDate, Customer, Item:

          Order123, 01/01/2013, ABC, ItemA,

          Order129, 01/26/2013, ABC, ItemA,

          Order130, 02/02/2013, ABC, ItemA,

          Order124, 01/01/2013, ABC, ItemB,

          Order128, 01/15/2013, ABC, ItemB,

          Order131, 02/16/2013, ABC, ItemB,

          Order125, 01/01/2013, DEF, ItemA,

          Order126, 01/05/2013, DEF, ItemA,

          Order127, 01/15/2013, DEF, ItemA

           

          Desired result after load script:

           

          OrderNumber, OrderDate, Customer, Item, DaysSincePreviousOrder:

          Order123, 01/01/2013, ABC, ItemA, 0

          Order129, 01/26/2013, ABC, ItemA, 25

          Order130, 02/02/2013, ABC, ItemA, 7

          Order124, 01/01/2013, ABC, ItemB, 0

          Order128, 01/15/2013, ABC, ItemB, 14

          Order131, 02/16/2013, ABC, ItemB, 31

          Order125, 01/01/2013, DEF, ItemA, 0

          Order126, 01/05/2013, DEF, ItemA, 4

          Order127, 01/15/2013, DEF, ItemA, 10