April 22, 2002 at 6:58 pm
Hello,
I appreciate the help I've received over the past on my queries, it's helped fully. This time I'm expanding my select statement. Below is the statement...
SELECT DISTINCT a.call_id, a.call_status_value,
dateadd(second, a.status_time_stamp,'1/1/1970 12:00:00 pm') as status_time_stamp
FROM status_tracking as a INNER JOIN
status_tracking as sub1 INNER JOIN
status_tracking as sub2
ON
sub1.call_id = sub2.call_id AND
sub1.call_status_value = '3 3RD RESPONDED' AND
sub2.call_status_value = '7 3RD RESOLVED'
ON
a.call_id = sub1.call_id INNER JOIN
unz_calls as uc
ON
a.call_id = uc.call_id
WHERE (a.call_status_value IN ('3 3RD RESPONDED', '7 3RD RESOLVED') OR
(a.call_status_value = '6 PENDING' AND
(a.status_time_stamp BETWEEN sub1.status_time_stamp AND sub2.status_time_stamp))) and uc.customer_name = 'ACC' and
uc.call_status = 'Closed'
ORDER BY a.call_id asc, status_time_stamp asc
It returns some data that looks like this...
Call_id Call_Status_Value Status_Time_Stamp
htnz00000584627 3 3RD RESPONDED 2002-03-22 11:14:48
htnz00000584627 6 PENDING 2002-03-22 16:45:18
htnz00000584627 7 3RD RESOLVED 2002-03-26 09:16:13
htnz00000584649 3 3RD RESPONDED 2002-03-21 16:50:32
htnz00000584649 7 3RD RESOLVED 2002-03-27 11:56:20
That's good, it's more than what I had before but for a particular call_id if there is a call status value that = '6 PENDING' then I don't want to see the entry for that call_id where the call_status_value = '7 3RD RESOLVED'. So the data I would like to see back looks something like the following...
Call_id Call_Status_Value Status_Time_Stamp
htnz00000584627 3 3RD RESPONDED 2002-03-22 11:14:48
htnz00000584627 6 PENDING 2002-03-22 16:45:18
htnz00000584649 3 3RD RESPONDED 2002-03-21 16:50:32
htnz00000584649 7 3RD RESOLVED 2002-03-27 11:56:20
If anyone could help on this I would name children after you.
Many thanks,
Chris
April 22, 2002 at 8:21 pm
Sorry can't remember what I was smoking in the last thread but after studying this again I believe this should do it.
SELECT DISTINCT
a.call_id,
a.call_status_value,
dateadd(second, a.status_time_stamp,'1/1/1970 12:00:00 pm') as status_time_stamp
FROM
status_tracking as a
INNER JOIN
status_tracking as sub1
INNER JOIN
status_tracking as sub2
ON
sub1.call_id = sub2.call_id AND
sub1.call_status_value = '3 3RD RESPONDED' AND
sub2.call_status_value = '7 3RD RESOLVED'
ON
a.call_id = sub1.call_id
INNER JOIN
unz_calls as uc
ON
a.call_id = uc.call_id
WHERE
(
a.call_status_value = '3 3RD RESPONDED' OR
(
a.call_status_value = '7 3RD RESOLVED' AND
a.call_id NOT IN (
SELECT
inStat.call_id
FROM
status_tracking inStat
WHERE
inStat.call_status_value = '6 PENDING' AND
inStat.call_id = a.call_id AND
(inStat.status_time_stamp BETWEEN sub1.status_time_stamp AND sub2.status_time_stamp)
)
) OR (
a.call_status_value = '6 PENDING' AND
(a.status_time_stamp BETWEEN sub1.status_time_stamp AND sub2.status_time_stamp)
)
) and
uc.customer_name = 'ACC' and
uc.call_status = 'Closed'
ORDER BY
a.call_id asc,
status_time_stamp asc
Note I do a subquery to check for items that would return the 6 PENDING result with the 7 3RD RESOLVED and if it exists then it should not output the 7 3RD RESOLVED item but check it to be sure the results where as expected (especially since I don't have a way to test). Oh an by the way.
quote:
If anyone could help on this I would name children after you.
Show them you love them and don't.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 22, 2002 at 9:02 pm
Ha, wonderful, it works like magic. Now I'll pull it apart to see exactly how you did it.
I'll go home tonoght and tell my wife that when we have kids one of them will be called James 🙂
Many thanks, as always.
Chris
April 22, 2002 at 9:03 pm
Ha, wonderful, it works like magic. Now I'll pull it apart to see exactly how you did it.
I'll go home tonoght and tell my wife that when we have kids one of them will be called James 🙂
Many thanks, as always.
Chris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply