Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: Comparing against next X in group

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
like image 432
SeanC Avatar asked May 07 '15 21:05

SeanC


People also ask

How do I compare data in next row in SQL?

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.

How do you find the difference between two consecutive rows in SQL?

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.

How do I find the difference between two values in SQL?

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.

Can you use datediff in a where clause in SQL?

Note: DATEADD and DATEDIFF SQL function can be used in the SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses.


1 Answers

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
like image 157
trouta Avatar answered Oct 22 '22 14:10

trouta