var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: average time to first appointment
I am designing a relational database for keeping appointment details. I have a parent
table with person details and a child page with appointment details. A simplistic
design of the two tables is shown below:
PersonID: (foriegn field)
I need to be able to calculate the average time taken from InitialContactDate to the
first appointment that occurs.
So for example if I have the following appointments:
person 1 24th may 2003
person 1 28th may 2003
person 2 25th may 2003
person 2 30th may 2003
If the InitialContactDate for person 1 was 23rd May 2003 and Person 2 was 22nd May
2003. The result should be average time to contact is 2 days (the average of 24th -
23rd and 25th - 22nd).
I need to be able to write a query that would give this result ie ignore all
appointments for a given person unless it is the first appointment (the lowest date)
and take the difference from the InitialContactDate for each person.
Can anyone advise a good way to acheive this.
I have enclosed a db that contains those two table names and a query with the example
Note the following:
1) relationships under - tools/relationships
2) I changed the fieldname of "date " to "calldate" - date is a reserved word and can create problems