OK, here's some background. The database I'm receiving an extract from is an in-house call-tracking system. Each call that comes in is assigned a ticket number (TICKET_NUMBER). Each ticket, though, may have several iterations (CALL_LEVEL) based on the number of updates a representative places on the ticket. Each iteration is represented by a different row. Any field that is updated on a call iteration is carried over to the next iteration until the ticket is closed. For example, we have ticket 12345 with 4 iterations:
Often, calls are assigned out to different groups to help with the resolution. These groups are listed in the ASSIGNEE column. I use the MODIFIED_DATE as the date the ticket was assigned to the assignee. The RETURN_DATE column stores the date when the issue is returned from the assignee to the representative.
Below is an example:
ID, TICKET_NUMBER, CALL_LEVEL, MODIFIED_DATE, STATUS, ASSIGNEE, RETURN_DATE
100, 12345, 1, 2008-06-27, OPEN, NONE, NULL
101, 12345, 2, 2008-06-29, PENDING, NONE, NULL
102, 12345, 3, 2008-06-30, ASSIGNED, GROUP_A, NULL
103, 12345, 4, 2008-07-01, ASSIGNED, GROUP_A, NULL
104, 12345, 5, 2008-07-03, CLOSED, GROUP_A, 2008-07-03
200, 67890, 1, 2008-06-18, OPEN, NONE, NULL
201, 67890, 2, 2008-06-18, PENDING, GROUP_Z, NULL
202, 67890, 3, 2008-06-19, PENDING, GROUP_Z, NULL
203, 67890, 4, 2008-06-20, PENDING, GROUP_Z, NULL
204, 67890, 5, 2008-06-25, PENDING, GROUP_Z, 2008-06-24
205, 67890, 6, 2008-06-26, CLOSED, GROUP_Z, 2008-06-24
My ultimate goal is to find the difference between the RETURN_DATE and the MODIFIED_DATE. I can get the RETURN_DATE from the final iteration of the call but am having trouble pulling the appropriate MODIFIED_DATE.
What type of query do I use to grab the MODIFIED_DATE from the first instance where a value in the ASSIGNEE column does not equal NONE (in these cases, CALL_LEVELs 3 and 2)? Please note that the STATUS has nothing to do with query.
Thanks for the help!