查找一天中花费的时间以及工作休息时间

本文介绍了查找一天中花费的时间以及工作休息时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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;

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