www.webdeveloper.com
Results 1 to 2 of 2

Thread: Help Averaging Time Spans

  1. #1
    Join Date
    Apr 2005
    Posts
    56

    Help Averaging Time Spans

    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!

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles