Days between Orders

Mon, 24 Jun 2013 15:02:18 GMT

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

Mon, 24 Jun 2013 15:04:55 GMT

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: