在SQL中获取两个连续行的时差

阿姆利特·辛格（Amrit Singh）：

我使用以下查询获取结果，如下所示：

SELECT
    calls.[Activity ID_18],
    calls.[Call SMS],
    calls.[Created By] PA_Name,
    calls.LOB,
    calls.Date,
    calls.StartDate Time_of_Call,
    calls.[Duration (min)] Duration,
    calls.[Call Connected?] 'Connected?'
FROM
    [dbo].[tblcallsnew] calls
WHERE
    CAST(calls.Date AS date) >= '3/15/2020' AND calls.LOB IN ('SO','EM')
ORDER BY
    PA_Name,CAST(calls.StartDate AS datetime2)

通过上面的查询，我得到以下结果：

Activity ID_18    Call SMS  PA_Name         LOB Date       Time_of_Call    Duration Connected?
00T2s000003U4uIEAS  Call    Aaron Fernandes EM  2020-03-16  2020-03-16 15:59:00 0.68    0
00T2s000003U4oMEAS  Call    Aaron Fernandes EM  2020-03-16  2020-03-16 16:02:00 0.07    0
00T2s000003U4z7EAC  Call    Aaron Fernandes EM  2020-03-16  2020-03-16 16:03:00 0.43    0
00T2s000003U59EEAS  Call    Aaron Fernandes EM  2020-03-16  2020-03-16 16:08:00 0.45    0
00T2s000003U517EAC  Call    Aaron Fernandes EM  2020-03-16  2020-03-16 16:10:00 3.37    1
00T2s000003U5MJEA0  Call    Aaron Fernandes EM  2020-03-16  2020-03-16 16:17:00 0.50    0
00T2s000003U5ApEAK  Call    Aaron Fernandes EM  2020-03-16  2020-03-16 16:18:00 0.08    0

我想要的是：我想获取每个PA NAME（代理）在两次连续呼叫之间经过的时间。想法是获得座席在两次呼叫之间花费的时间。

任何帮助，将不胜感激。

戈登·利诺夫（Gordon Linoff）：

您可以使用lead()：

select c.*,
       lead(startDate) over (partition by pa_name order by startDate) as next_startDate,
       datediff(minute, startDate,
                lead(startDate) over (partition by pa_name order by startDate)
               ) as minutes_between        
from [dbo].[tblcallsnew] c
where c.Date  >= '2020-03-15' and c.LOB in ('SO','EM')

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。