- May 27, 2002
- 12,653
- 205
- 106
I am using tables to track a "permit application" process along its path, and I need help with my querys.
Structure...
Table 1 contains identification information.
Name, Application Number, etc.
Table 2 contains tracking information.
Auto#, Application Number, Date, Event, Comments
Possible Event List:
1 Application Recieved
2-5 Sent to Mgr <X,Y,Z,A>
6-9 Feedback Recieved back from Mgr <X,Y,Z,A>
10 Denial Letter Sent
11 Reapplication with Changes/Corrections
12 Approval Letter Sent
Im really only concerned with events 1,10,11,12. I need to be able to tell the time difference in days from any event 1 or 11 to the NEXT 10 or 12. Our response times have to be under 21 days, with any number of resubmission cycles allowed.
The path could go 1->12 or 1->10->11->12 or 1->10->11->10->11->12
or even 1->10 && 1->10->11->10 with no resubmission.
For Example table 2 contains (ignoring events 2-9):
1. App# 1. Recieved 6/1/10.
2. App# 2. Recieved 6/2/10.
3. App# 1. Denied 6/15/10.
4. App# 1. Resubmitted 6/18/10.
5. App# 2. Approved 6/19/10.
6. App# 1. Denied 6/21/10.
7. App# 1. Resubmitted 6/25/10.
8. App# 1. Approved 6/30/10.
How can I query time from Line 1 to 3, 2 to 5, 4 to 6, 7 to 8.
I tried parallel queries (query 1: APP# and Event(1,11)) (query 2: APP# and Event(10,12))...related by app number... but I seem to get additional unwanted lines like time from 1 to 6, 1 to 8, 4 to 3, 4 to 8, 7 to 3, 7 to 6, and its screwing up the averages. It seems im missing some other necessary relationship.
Structure...
Table 1 contains identification information.
Name, Application Number, etc.
Table 2 contains tracking information.
Auto#, Application Number, Date, Event, Comments
Possible Event List:
1 Application Recieved
2-5 Sent to Mgr <X,Y,Z,A>
6-9 Feedback Recieved back from Mgr <X,Y,Z,A>
10 Denial Letter Sent
11 Reapplication with Changes/Corrections
12 Approval Letter Sent
Im really only concerned with events 1,10,11,12. I need to be able to tell the time difference in days from any event 1 or 11 to the NEXT 10 or 12. Our response times have to be under 21 days, with any number of resubmission cycles allowed.
The path could go 1->12 or 1->10->11->12 or 1->10->11->10->11->12
or even 1->10 && 1->10->11->10 with no resubmission.
For Example table 2 contains (ignoring events 2-9):
1. App# 1. Recieved 6/1/10.
2. App# 2. Recieved 6/2/10.
3. App# 1. Denied 6/15/10.
4. App# 1. Resubmitted 6/18/10.
5. App# 2. Approved 6/19/10.
6. App# 1. Denied 6/21/10.
7. App# 1. Resubmitted 6/25/10.
8. App# 1. Approved 6/30/10.
How can I query time from Line 1 to 3, 2 to 5, 4 to 6, 7 to 8.
I tried parallel queries (query 1: APP# and Event(1,11)) (query 2: APP# and Event(10,12))...related by app number... but I seem to get additional unwanted lines like time from 1 to 6, 1 to 8, 4 to 3, 4 to 8, 7 to 3, 7 to 6, and its screwing up the averages. It seems im missing some other necessary relationship.
Facebook
Twitter