Member Avatar for qdaddyo

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!

  • 2 Contributors
  • 1 Reply
  • 72 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by tesuji
Member Avatar for tesuji

Hi qdaddyo

you may try this statement:

with 

modi (tn, moda) as
  (select ticket_number, min(modified_date) from tictac 
     where assignee <> 'NONE' group by ticket_number) ,

retu (tn, reda) as
  (select ticket_number, max(return_date) from tictac 
     group by ticket_number)

select m.tn, m.moda, r.reda, datediff(day, m.moda, r.reda) as "days" 
   from modi m join retu r on m.tn = r.tn;

Not just tested, so tell me whether it's working on sql server. Instead of min()/max() top 1 together with appropriate sorting should also work.

krs,
tesu

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.