# Database Discussions > MySQL >  help calculating average time spans

## Natcon67

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!

----------


## stecal

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

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

----------


## Natcon67

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?

----------


## barry9

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

----------

