查找一天中花费的时间以及需要休息的时间

Question

我处在某种情况下，我需要找出在办公室进行一些内部申请所花费的总时间。

我有这样的示例数据：

Id  EmployeeId  ScanDateTime    Status
 7  87008   2018-08-02 16:03:00.227 1
 8  87008   2018-08-02 16:06:17.277 2
 9  87008   2018-08-02 16:10:37.107 3
 10 87008   2018-08-02 16:20:17.277 2
 11 87008   2018-08-02 16:30:37.107 3
12  87008   2018-08-02 20:06:00.000 4

这里的状态有不同的含义：

1开始2暂停3恢复4结束

表示员工在状态为1时在ScanDateTime开始工作。他们可以休息一下（状态2），然后返回并继续他们的工作（状态3），状态为4表示他们正在结束工作。注意：工作时间可能会有多个休息时间。

预期产量：

EmployeeId  StartTime                 EndTime                  BreakInMins 
87008       2018-08-02 16:03:00.227   2018-08-02 20:06:00.000   14

我试图按照一些示例来计算预期的结果集，但无济于事。

在类似的示例可用的地方，我找不到任何这样的示例。

任何帮助，将不胜感激。

Answer 1

请尝试这个。处理多个休息/员工和个案，当休息仍在进行中或会话未完成时

select
     [EmployeeId]   =   [s].[EmployeeId]
    ,[StartTime]    =   [s].[ScanDateTime]
    ,[EndTime]      =   [et].[ScanDateTime]
    ,[BreakInMins]  =   [b].[BreakInMins]
from
    [Scans] as  [s] --  here is your table
outer apply
    (      
        select top 1 [ScanDateTime], [Id] from [Scans] where [Id] > [s].[Id] and [EmployeeId] = [s].[EmployeeId] and [Status] = 4 order by [ScanDateTime] asc
    )       as  [et]
outer apply
    (
        select
              [BreakInMins] = sum(isnull([r].[mins], datediff(mi, [sp].[ScanDateTime], getdate())))
        from
            [Scans] as [sp]
        outer apply
            (
                select top 1 [mins] = datediff(mi, [sp].[ScanDateTime], [ScanDateTime]) from [Scans] where [Id] > [sp].[Id] and [EmployeeId] = [sp].[EmployeeId] and [Status] IN (3, 4) order by [ScanDateTime] asc
            ) as [r]
        where
                [sp].[id] > [s].[id] and [sp].[id] < isnull([et].[id], [id] + 1)
            and [sp].[EmployeeId] = [s].[EmployeeId]
            and [sp].[Status] = 2

    )       as  [b]    
where
        [Status] = 1;

这是易于测试的脚本：脚本