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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 5 down vote accepted
select item, 
from (
  select item, 
         lead(move_date)  over (partition by item order by move_date) - move_date as duration,
  from movement         
) t
group by item, moved_to
order by item, moved_to;

SQLFiddle example:!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


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.