Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
WITH act_log_ack as
(
SELECT
call_req_id,
analyst,
time_stamp,
CAST(action_desc AS nvarchar(max)) 'action_desc',
type,
row_number() over
( partition BY call_req_id
ORDER BY last_mod_dt ) rn
FROM
act_log
WHERE
DATEADD(ss, time_stamp + -14400, '01-JAN-1970') >= '01/01/2010 12:00 AM'
AND DATEADD(ss, time_stamp + -14400, '01-JAN-1970') <= '07/31/2010 11:59 PM'
AND type = 'ST'
AND action_desc LIKE 'Status changed from ''Open'' %'
),
act_log_tran AS
(
SELECT
call_req_id,
analyst,
time_stamp,
CAST(action_desc AS nvarchar(max)) 'action_desc',
type,
row_number() over
( partition BY call_req_id
ORDER BY last_mod_dt ) rn
FROM
act_log
WHERE
DATEADD(ss, time_stamp + -14400, '01-JAN-1970') >= '01/01/2010 12:00 AM'
AND DATEADD(ss, time_stamp + -14400, '01-JAN-1970') <= '07/31/2010 11:59 PM'
AND type = 'TR'
AND action_desc LIKE '% to ''Tier 2''%'
)
SELECT
A.call_req_id,
A.time_stamp,
A.rn
FROM
act_log_ack A
INNER JOIN act_log_tran T
ON A.call_req_id = T.call_req_id
LEFT JOIN ca_contact ANL
ON A.analyst = ANL.contact_uuid
WHERE
T.rn = 1
AND A.time_stamp > T.time_stamp
AND A.analyst IN
(
SELECT
MEM.contact_uuid
FROM
grpmem
INNER JOIN ca_contact GRP
ON grpmem.group_id = GRP.contact_uuid
INNER JOIN ca_contact MEM
ON grpmem.member = MEM.contact_uuid
WHERE
GRP.last_name = 'Tier 2'
)
GROUP BY
A.call_req_id,
A.time_stamp,
A.rn
ORDER BY
1
RESULTS extract
call_req_id time_stamp rn
cr:1041066083 1269611394 1
cr:1044845740 1263916396 2
cr:1044846255 1263914579 1
cr:1044861103 1264012249 1 <-
cr:1044861103 1264021382 2 <-
cr:1044861478 1264012015 1
cr:1044862323 1264011596 1
cr:1044862611 1264010784 1
cr:1044862692 1264010537 1
cr:1044862801 1264010246 1
cr:1044863089 1264009815 2
cr:1044863243 1264009049 1
cr:1044863500 1264008896 1
cr:1044893316 1272025369 4 <-
cr:1044905914 1266506905 3
cr:1044914094 1263913411 2
call_req_id time_stamp rn
cr:1041066083 1269611394 1
cr:1044845740 1263916396 2
cr:1044846255 1263914579 1
cr:1044861103 1264021382 2 <-
cr:1044861103 1264012249 1 <-
cr:1044861478 1264012015 1
cr:1044862323 1264011596 1
cr:1044862611 1264010784 1
cr:1044862692 1264010537 1
cr:1044862801 1264010246 1
cr:1044863089 1264009815 2
cr:1044863243 1264009049 1
cr:1044863500 1264008896 1
cr:1044893316 1272025369 4
cr:1044905914 1266506905 3
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Open in new window