March 27, 2002 at 9:56 pm
Hi,
I've a (I think complicated) select statement, which I've had prior help on. However now I want to take it one step further. My select statement is the following...
select distinct a.call_id,
a.customer_name,
a.call_status_value,
dateadd(second, a.status_time_stamp,'1/1/1970 12:00:00 pm') as status_time_stamp,
a.call_status,
a.create_date
from (select unz_calls.call_status,
status_tracking.call_id,
status_tracking.call_status_value,
unz_calls.customer_name,
status_tracking.status_time_stamp,
unz_calls.create_date
from status_tracking inner join unz_calls on status_tracking.call_id = unz_calls.call_id
where call_status_value in ('3 3RD RESPONDED', '7 3RD RESOLVED','6 PENDING')) a,
(select * from status_tracking where call_status_value = '3 3RD RESPONDED') b,
(select * from status_tracking where call_status_value = '7 3RD RESOLVED') c
where a.call_id = b.call_id and a.call_id = c.call_id
order by a.call_id asc
It returns some data like the following...
Call_ID Customer_Name Call_Status_Value Status_Time_Stamp
htnz00000575141 ACC 3 3RD RESPONDED 2002-03-14 08:55:11
htnz00000575141 ACC 6 PENDING 2002-03-11 10:03:47
htnz00000575141 ACC 7 3RD RESOLVED 2002-03-27 10:03:16
The above data is good as the call status values are in order of date. Every call_id has an entry for 3 3RD RESPONDED and 7 3RD RESOLVED and could possibly have an entry for 6 PENDING. The following data however is not what I want but what I can possibly get..
htnz00000577536 ACC 3 3RD RESPONDED 2002-03-10 07:59:21
htnz00000577536 ACC 6 PENDING 2002-03-11 13:46:25
htnz00000577536 ACC 7 3RD RESOLVED 2002-03-11 09:05:38
As you can see the date time for 6 PENDING is outside those of 3 3RD RESPONDED and 7 3RD RESOLVED. If this is the case I don't want to see the 6 PENDING row for that call_id. I just can't figure out where to start with this one. If anyone has any ideas it would be much appreciated.
Cheers,
Chris
March 28, 2002 at 10:21 am
Try a where clause that does not return rows that are 6 pending when the time is greater than the time in table b (3rd responded)
Steve Jones
April 1, 2002 at 1:07 pm
Try this, I did change a bit to conform more to TSQL format and hopefuly make easier to read. This should handle it.
SELECT
a.call_id,
uc.customer_name,
a.call_status_value,
dateadd(second, a.status_time_stamp,'1/1/1970 12:00:00 pm') as status_time_stamp,
uc.call_status,
uc.create_date
FROM
status_tracking a
INNER JOIN
status_tracking sub1
INNER JOIN
status_tracking sub2
ON
sub1.call_id = sub2.call_id AND
sub1.call_status_value = '3 3RD RESPONDED' AND
sub2.call_status_value = '7 3RD RESPONDED'
ON
a.call_id = sub1.call_id
INNER JOIN
unz_calls 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)
/* I was assuming should be greater then 3 time and less than 7 time. */
)
ORDER BY
a.call_id asc
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 1, 2002 at 1:27 pm
Good morning,
Thanks for your assistance on this one it's been a big help. The formula now works as it should.
Thanks again,
Chris
April 1, 2002 at 3:01 pm
Great to hear.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply