T-SQL 如何从中获得首次调用解决方案 [英] T-SQL How to get First Call Resolution out of this

问题描述

我有两列.一个是电话号码.第二个是电话的确切日期和时间(开始).我想为 FCR 添加第三列(如果为假，则值为 0，如果为真，则值为 1).方法是...如果数字1"在24小时内没有回调，则FCR = 1".如果 2 号在接下来的 24 小时内回拨，则FCR = 0"但是我的查询有问题，主要是它的逻辑.

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.

感谢任何帮助.

推荐答案

看起来您想检查电话号码是否在 24 小时内再次出现.下面的例子应该会让你走上正轨.我使用了 OUTER APPLY 结构.

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 ('[phone]', dateadd(hour,-30,getdate()));
insert into @table values ('[phone]', dateadd(hour,-3,getdate()));
insert into @table values ('[phone]', 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

这给了我:

PhoneNumber     EntryTime                NewCall                    FCR
[phone]  2018-04-15 07:13:37.283  2018-04-16 10:13:37.283    0
[phone]  2018-04-16 10:13:37.283  NULL                       1
[phone]  2018-04-16 11:13:37.283  NULL                       1

这篇关于T-SQL 如何从中获得首次调用解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！