T-SQL How to get First Call Resolution out of this

Question

I have two columns. One is a phone number. Second one is the exact date and time of the phone call (start of it). I want to add a third column for FCR (values 0 if false, 1 if true). Method is...if "number 1" didn't callback in 24 hours, then "FCR = 1". If number 2 called back within next 24 hours, then "FCR = 0" But I am having issues with my query with this, mainly with the logic with it.

Any help appreciated.

How do you see what is ingoing and what is outgoing? How would you detect the callback? — Shnugo, Apr 16 '18 at 10:28
Are you trying to determine when the "phone number", the first column, was called again to populate the FCR column appropriately? Also, include the TSQL of what you've tried so far and the DDL for the table — Mazhar, Apr 16 '18 at 10:28
I have a separate column that defines if the call is In or Out. Sanders solution worked perfectly for me. Just needed to modify it a bit. — NihilisticWonder, Apr 16 '18 at 13:23

score 0 · Accepted Answer · answered Apr 16 '18 at 11:16

Looks like you want to check if a phone number reoccurs within 24h. The example below should set you on your way. I used an OUTER APPLY construction.

declare @table table (
    PhoneNumber nvarchar(20),
    EntryTime datetime
)

insert into @table values ('(321) 546-7842', dateadd(hour,-30,getdate()));
insert into @table values ('(321) 546-7842', dateadd(hour,-3,getdate()));
insert into @table values ('(251) 546-9442', dateadd(hour,-2,getdate()));

select  t1.PhoneNumber,
        t1.EntryTime,
        t3.NewCall,
        case when datediff(hour, t1.EntryTime, t3.NewCall) > 24 then 0 else 1 end as 'FCR'
from @table t1
outer apply (   select top 1 t2.EntryTime as 'NewCall'
                from @table t2
                where t2.PhoneNumber = t1.PhoneNumber
                  and t2.EntryTime > t1.EntryTime
                order by t2.EntryTime ) t3

This gives me:

PhoneNumber     EntryTime                NewCall                    FCR
(321) 546-7842  2018-04-15 07:13:37.283  2018-04-16 10:13:37.283    0
(321) 546-7842  2018-04-16 10:13:37.283  NULL                       1
(251) 546-9442  2018-04-16 11:13:37.283  NULL                       1

This is perfect. Just needed to modify it a bit to exclude duplicates. Thank you. — NihilisticWonder, Apr 16 '18 at 11:43
Also needed to turn around the case statement and take care of NULL values ">24 then 1 when is null then 1 else 0 end" — NihilisticWonder, Apr 16 '18 at 12:35

T-SQL How to get First Call Resolution out of this

1 Answers1

Linked