Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 5 down vote accepted
select item, 
       sum(duration),
       moved_to
from (
  select item, 
         lead(move_date)  over (partition by item order by move_date) - move_date as duration,
         moved_to
  from movement         
) t
group by item, moved_to
order by item, moved_to;

SQLFiddle example: http://www.sqlfiddle.com/#!4/dff2c/1

share|improve this answer
    
That's right. Lets keep your answer. –  Nicholas Krasnov Oct 8 '12 at 17:28
    
@NicholasKrasnov: oh! Sorry, I didn't mean to rival your answer. –  a_horse_with_no_name Oct 8 '12 at 17:30
    
It's Ok. keeping two identical answers would be a redundancy. Especially when mine had a bug. +1 :) –  Nicholas Krasnov Oct 8 '12 at 17:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.