help calculating average time spans

Hello,
This is the second time posting here. Thanks again to oraclable!

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!

http://download-west.oracle.com/docs...13.htm#1008373

Start with this and try some code on your own. Also look at date math.

is there something specific you wanted me to get from that? I browsed through it and nothing seems relevant to my situation. Also, I know this is the MySQL part of the forums; do you have a good reference to some MySQL documentation i could use?

thought this was an interesting question, figured I'd try my hand:

SELECT AVG(TO_DAYS(b.date)-TO_DAYS(a.date)) FROM applicant_tracking a JOIN applicant_tracking b ON a.applicant_id = b.applicant_id AND a.status_id = b.status_id-1;

This should give you overall average step-to-step duration. For explicit steps, just remove the 'AND a.status_id=b.status_id-1' and add a where clause specifying the first step in table a and the second step in table b (e.g. 'WHERE a.status_id = 1 AND b.status_id = 2'). this way you can also find average days difference between say steps 1 and 3 or steps 2 and 5.

HTH
thanx:barry