我需要了解一些内部应用程序在办公室花费的总时间.
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;这是测试友好的脚本:脚本
更多推荐
查找一天中花费的时间以及工作休息时间
发布评论