Help Averaging Time Spans

[Natcon67]

Hello,
I could really use some help understanding how to structure my query.

Alright, in our system we have 'applicants'. We have 5 stages that we move our applicants through. each time an applicant moves into a new stage we insert a new row in a table called applicant_tracking with the applicant_id, status_id, and date. An example of the table is this:

at_id | applicant_id | status_id | date
+ -----------------------------------------------
| 1 30 1 2005-10-10
| 2 30 2 2005-10-11
| 3 30 3 2005-10-12
| 4 30 4 2005-10-13
| 5 31 1 2005-10-15
| 6 31 2 2005-10-17
| 7 31 3 2005-10-19
| 8 32 1 2005-10-09
| 9 32 2 2005-10-15
| 10 33 1 2005-10-11
| 11 34 1 2005-10-20


I would like to make a query that would give me the average time it takes to move the applicants from one stage to the next. I dont mind having to do a separate query for each stage movement but I cant think of how to even do it for just one stage change.

I really appreciate any help you can give!

[chazzy]

first try doing a join on the table with itself.

The where conditions should have a.applicant_id = b.applicant_id AND a.status_id - b.status_id = 1 and a.date > b.date
the actual select part should select the difference in dates between the two. then you can just take the average of that.

note, i can't give any actual code as it depends on what database you use.