Results 1 to 4 of 4

Thread: help calculating average time spans

  1. #1
    Join Date
    Jan 2006
    Posts
    7

    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!

  2. #2
    Join Date
    Oct 2005
    Posts
    2,557
    http://download-west.oracle.com/docs...13.htm#1008373

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

  3. #3
    Join Date
    Jan 2006
    Posts
    7
    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?

  4. #4
    Join Date
    Jan 2006
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •