This is simplified but represents the problem I am trying to solve. We have a table containing 5-10 million rows in a format similar to the following...
Input Table
Date Item Moved to Box
Oct-1 1 BoxA
Oct-6 1 BoxB
Oct-8 1 BoxC
Oct-9 1 BoxB
Oct-16 1 BoxC
Oct-17 1 BoxD
And I am trying to convert it into this
Expected Output
Item Box Duration
1 BoxA 5
1 BoxB 9
1 BoxC 2
1 BoxD *unimportant
*It doesn't matter what the query returns for BoxD
(the only box move in without a box move out) as it is discarded.
For the example hopefully you can see that the input table represents a log of when an item moves from box to box and the expected output is cumulitively how long each item spends in each box.
My first thought was to do a table join with itself and do some date min/maxing for each record to try to find the exit date from a box, then sum the results but it seems pretty process intensive.
How would someone approach this in an efficient way?