时间戳

首页 > 解决方案 > 从登录时间和注销时间为不同行的表中计算持续时间

sql - 从登录时间和注销时间为不同行的表中计算持续时间

问题描述

我有一个包含代理登录信息的表（ADR）。每次代理登录或注销时都有一行。代理每天可以多次登录和注销。EventID =11 表示已登录，eventid=21 表示已注销。所需的输出是每个登录和注销的单行，其中包含登录和注销时间和持续时间。如果当天的第一个事件已注销，则表示代理已在前一天登录并在今天注销。因此，持续时间将从一天开始到他注销的时间计算。同样，如果最后一个事件是在当天记录的，则计算从该时间到该天结束的持续时间。

数据：

这是来自表 ADR 的输入数据

这是所需的输出

我尝试使用 case 语句，然后只选择需要的行。尚未完全开发查询。发布我要尝试的内容。

select a.AgentID,a.QueueID,case when a.rownum =1 and  a.laststateduration=0 then a.EventTime
                        else when a.rownum =1 and a.laststateduration<>0  then CAST(CAST(a.EventTime as date) as datetime) end as LogonDatetime,
                        case when a.rownum=1 and  a.laststateduration=0 
then a.EventTime ....
from (select *,ROW_NUMBER() over (order by EventTime asc) as rownum from ADR
where EventID in (11,21)
and convert(date,EventTime) = '2021-02-02'
and AgentID =1006
and QueueID in (6017,6018)) a

标签： sqlsql-serverduration

解决方案

我建立在 Gorden Linoff 的解决方案之上。使用 if 条件检查第一行是否为登录/注销，并使用默认值的LAG函数获取登录时间，同样使用默认值的LEAD函数获取注销时间。最后使用 where 条件删除重复项。

select a.*, datediff(second, a.Logon, a.Logoff) as Duration
from (
                select  AgentID, QueueID,eventid, iif(eventid =21, lag(eventtime,1, cast(cast(EventTime as Date) as datetime)) over (partition by convert(date,EventTime),agentid, queueid order by eventtime),
                                    EventTime) as logon , 
                                iif(eventid =11, lead(eventtime,1,DATEADD(Minute, -1,cast(cast(EventTime+1 as Date) as datetime))) over (partition by convert(date,EventTime),agentid, queueid order by eventtime),
                                    EventTime) as Logoff
                from ADR
                where eventid in (11, 21)
        ) a
where a.eventid =11 or (a.eventid =21 and CAST(a.logon as time)='00:00:00.0000000')

推荐阅读