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.

Answer 1

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