I know I can compare previous/next rows with LAG
and LEAD
, but in the comparison that I am doing; I need to be able to skip rows occasionally (but still need to acknowledge them)
Specifics: I am trying to track consults, and warm and cold transfers in a call environment. Each call has a unique key, and an ascending employee number for each stage. It also has the start and end times for each segment of the call.
A consult is defined as a call that starts and finishes before the previous non consult segment ends.
A Warm transfer is one where the end of the previous segment is after the start of the previous segment, and ends after the end of the previous segment.
A Cold transfer is a segment that starts after the end of the previous segment.
Example Data:
topcallid e_created e_terminated empnum EG995GIFM 16:22:40.933 16:29:51.010 1 EG995GIFM 16:25:59.827 16:27:49.027 2 EG995GIFM 16:30:07.453 16:37:44.500 3 EG995GIFM 16:38:01.677 16:59:30.777 4 EG995GIFM 16:59:46.737 17:16:48.397 5 EG995GIFM 17:04:51.243 17:29:21.620 6
Desired output:
topcallid.......TransStatus
EG995GIFM Consult (Stage 1 To 2)
EG995GIFM Cold Transfer (Stage 1 To 3)
EG995GIFM Cold Transfer (Stage 3 To 4)
EG995GIFM Cold Transfer (Stage 4 To 5)
EG995GIFM Warm Transfer (Stage 5 To 6)
Current Code, which gives every combination comparing every stage to all that follow:
SELECT ta.topcallid,
CASE
WHEN ta.e_created < Trans.e_created AND ta.e_terminated > Trans.e_terminated THEN 'Consult (Stage ' + CAST(TA.EmpNum AS VARCHAR(3)) + ' To ' + CAST(Trans.EmpNum AS VARCHAR(3)) + ')'
WHEN ta.e_terminated < Trans.e_created THEN 'Cold Transfer (Stage ' + CAST(TA.EmpNum AS VARCHAR(3)) + ' To ' + CAST(Trans.EmpNum AS VARCHAR(3)) + ')'
WHEN ta.e_terminated > Trans.e_created THEN 'Warm Transfer (Stage ' + CAST(TA.EmpNum AS VARCHAR(3)) + ' To ' + CAST(Trans.EmpNum AS VARCHAR(3)) + ')'
END TransStatus
FROM [TransferTypeAnalysis] TA
JOIN [TransferTypeAnalysis] Trans
ON TA.topcallid = Trans.topcallid
AND ta.empnum < Trans.empnum
ORDER BY
TA.topcallid,
ta.empnum
One of the easiest ways, to compare this is using the lag function. The lag function will allow you to shift the rows downward so that you can view these rows as one observational row. Here is a simple example of how to use the lag function to shift rows downward.
In the blue text, you can see the calculation of the SQL delta between two rows. To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function.
The DIFFERENCE() function compares two SOUNDEX values, and returns an integer. The integer value indicates the match for the two SOUNDEX values, from 0 to 4. 0 indicates weak or no similarity between the SOUNDEX values.
Note: DATEADD and DATEDIFF SQL function can be used in the SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses.
It appears you need a subquery to compare to the previous terminated call. This logic assumes the terminated times are unique per call id.
select
t3.topcallid,
case when t3.e_created < t4.e_created and t3.e_terminated > t4.e_terminated then 'Consult (Stage ' + cast(t3.EmpNum as varchar(3)) + ' To ' + cast(t4.EmpNum as varchar(3)) + ')'
when t3.e_terminated < t4.e_created then 'Cold Transfer (Stage ' + cast(t3.EmpNum as varchar(3)) + ' To ' + cast(t4.EmpNum as varchar(3)) + ')'
when t3.e_terminated > t4.e_created then 'Warm Transfer (Stage ' + cast(t3.EmpNum as varchar(3)) + ' To ' + cast(t4.EmpNum as varchar(3)) + ')'
end TransStatus
from
[TransferTypeAnalysis] t3
INNER JOIN
(
select
t1.topcallid, t1.empnum, t1.e_created, t1.e_terminated, max(t2.e_terminated) previous_term_dt
FROM
[TransferTypeAnalysis] t1
left join [TransferTypeAnalysis] t2
on t1.topcallid=t2.topcallid and t1.empnum>t2.empnum
GROUP BY t1.topcallid, t1.empnum, t1.e_created, t1.e_terminated
) as t4 on t3.topcallid = t4.topcallid and t3.e_terminated = t4.previous_term_dt
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With