查找一天中花费的时间以及工作休息时间 [英] Find time spent in a day along with work break taken

问题描述

我需要了解一些内部应用程序在办公室花费的总时间.

I am in a situation where I need to find-out total time spent in office for some internal application.

我有这样的样本数据:

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

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

Here Status have different meanings:

1- 开始2-暂停3- 简历4-结束

1- Start 2- Pause 3- Resume 4- End

表示当状态为 1 时，员工在 ScanDateTime 开始工作.他们可以休息(状态 2)然后回来继续工作(状态 3)，状态 4 表示他们正在结束工作.注意:工作时间可能会有多次休息.

Means Employees start their work at ScanDateTime when status is 1. They can go for break(status 2) and come back and resume their work(Status 3) and with status 4 means they are ending their job. Note: There could be multiple breaks during work hours.

预期输出:

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

我试图按照一些例子来计算预期的结果集，但没有帮助.

I have tried to follow some example to calculate the expected result set but not helping.

我找不到任何可以使用此类似示例的示例.

I could not find any such example where this similar example available.

任何帮助将不胜感激.

推荐答案

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

Please try this. Handles multiple breaks/employees and cases, when break is still in progress or session is not finished

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;

这是测试友好的脚本:脚本

这篇关于查找一天中花费的时间以及工作休息时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！