SQL Stumper

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 403,519 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,814 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 347 | Replies: 1

•

Join Date: Jul 2008

Posts: 1

Reputation: qdaddyo is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

qdaddyo

Offline

Newbie Poster

SQL Stumper

Jul 7th, 2008

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!

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: SQL Stumper

Jul 7th, 2008

Hi qdaddyo

you may try this statement:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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;
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

Last edited by tesuji : Jul 7th, 2008 at 8:25 pm.

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.