计算登录-中断- LogoutTimes

Question

我有一个有3列的表

AgentID    Status       EventTime
-----------------------------------------
agent1     Login        20150101 08:30
agent1     Break        20150101 11:00
agent1     Available    20150101 12:00
agent1     WorkOffline  20150101 3:00
agent1     logout       20150101 4:00
agent2     Login        20150101 08:30
agent2     Break        20150101 11:00
agent2     Available    20150101 12:00
agent2     WorkOffline  20150101 3:00
agent2     logout       20150101 4:00

我正在尝试计算每种事件类型之间的间隔。基本上创建另一个包含4列的表：

agent   TimeAvailable     TimeonBreak     TimeWorkingOffline

任何想法都将不胜感激。我已经在网上看到了一些关于登录和注销逻辑的想法，通过使用两个表，一个用于登录，一个用于注销，但我不明白它在这里是如何应用的，谢谢你的建议……

Answer 1

您使用的是哪种RDBMS？一般来说，非常低效的sql可能是这样的：

SELECT 
        Agent
        ,EventTime as LoginTime
        ,(
            SELECT MIN(EventTime)
            FROM SessionTable S2
            WHERE
                S2.Status = 'Break'
                AND S2.Agent = S.Agent
                AND S2.EventTime > S.EventTime
        )As NextBreak
        ,.. datediff between LoginTime and NextBreak
    FROM 
        SessionTable S
    WHERE
        Status = 'Login'

我希望你能理解idea。

Answer 2

有点冗长，但应该做你想做的(基于你的数据似乎流动的方式)：

select
    AgentID,
    DATEDIFF(hh,
            (select 
                EventTime 
             from yourTable i2
             where i2.Status = 'Available' and i2.AgentID = i1.agentID),
             (select 
                EventTime 
             from yourTable i2
             where i2.Status = 'WorkOffline' and i2.AgentID = i1.agentID)) as TimeAvailable,
    DATEDIFF(hh,
            (select 
                EventTime 
             from yourTable i2
             where i2.Status = 'Break' and i2.AgentID = i1.agentID),
             (select 
                EventTime 
             from yourTable i2
             where i2.Status = 'Available' and i2.AgentID = i1.agentID)) as TimeOnBreak,
    DATEDIFF(hh,
            (select 
                EventTime 
             from yourTable i2
             where i2.Status = 'WorkOffline' and i2.AgentID = i1.agentID),
             (select 
                EventTime 
             from yourTable i2
             where i2.Status = 'logout' and i2.AgentID = i1.agentID)) as TimeWorkingOffline 
from yourTable i1
group by AgentID