SQL Stumper

Last Post 07 Jul 2008 01:42 PM by qdaddyo. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
qdaddyo
New Member
New Member

--
07 Jul 2008 01:42 PM
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:

TICKET_NUMBER, CALL_LEVEL
12345, 1
12345, 2
12345, 3
12345, 4

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!


Acceptable Use Policy
---