使用多个输入/输出计算总工时的存储过程。 [英] Stored procedure to calculate total work hours with multiple in/out.
问题描述
寻找存储过程来计算具有多个输入和输出的员工的总工作时间
表结构
Id - primary let
EmpCode - 员工代码
状态 - 输入/输出
ActionDatetime - 日期时间
样本记录<前lang = text> 1 emp100在2017-05-18 10:00
2 emp100 OUT 2017-05-18 15:00
3 emp100在2017-05-18 17:00
4 emp100 OUT 2017-05-18 20:00
5 emp102 IN 2017-05-18 06:00
6 emp102 OUT 2017-05-18 10:00
7 emp103 in 2017-05 -18 07:00
8 emp103 IN 2017-05- 18 10:00
我必须对上述数据进行抽样。
我想运行带日期的存储过程。
它应该给出结果如。
1.员工代码列表，不重复员工代码（员工代码不能在行中重复）
2.所有员工的IN和Out都是如此。
1. Emp100在2017-05-18 10:00 OUT 2017-05-18 15:00在2017-05-18 17:00 OUT 2017-05-18 20:00（总计小时）
如果员工前任或外出任何进出，奇数进出。同样继续相同状态的相同序列也在一天的开始时没有它应该在最后一列显示为备注。
这个职位是从移动设备发布的，所以请原谅任何错别字或任何遗漏的细节。
我尝试过：
USE [test]
GO
/ * *****对象：StoredProcedure [ dbo]。[GetWorkingHours]脚本日期：19-05-2017 12:07:55 ****** /
SET < span class =code-sdkkeyword> ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALT ER PROCEDURE [dbo]。[GetWorkingHours]
@ Day date
AS
BEGIN
SET NOCOUNT ON ;
EmpIn as （
select row_number（） over （ order 按 [日期]）RowNum，[日期] as ' Date'，EmpId as ' EmpId'
来自 EmpLog（ nolock ）
其中 cast（[日期] as date ）= @ Day 和 [状态] LIKE ' ％IN％'
），
EmpOut as （
select row_number（） over （订单 按 [日期]）RowNum，[日期] as ' Date' ，EmpId as ' EmpId'
来自 EmpLog（ nolock ）
其中强制转换（[日期] date ）= @ Day 和 [Status] LIKE ' ％OUT％'
）
选择
e1.EmpId as ' EmpId'，
e1。日期 as ' 在'，
e2。日期 as ' Out'，
datediff（MINUTE，e1。[日期]，e2。[日期]） as ' TotalHours'
来自 EmpIn e1
完整 外部 join EmpOut e2 on e1.RowNum = e2.RowNum
end
我们不做你的功课：这是有原因的。它就是为了让你思考你被告知的事情，并试着理解它。它也在那里，以便您的导师可以识别您身体虚弱的区域，并将更多的注意力集中在补救措施上。
所以试一试，看看有多远你可以得到 - 如果你遇到一个具体的问题，那么请询问，我们会尽力帮助你。但我们不打算为你做这一切！
我去了。正如我在评论中所说，我希望这不是功课，因为我发现很难获得你想要的所有信息。
第一个要处理的问题是缺少的信息。处理这种情况的最佳方法是生成一系列预期条目，例如：SELECT DISTINCT EmpCode， ' IN' AS [Status] FROM InOut WHERE CAST（ActionDatetime AS 日期）= @ Day
UNION ALL
SELECT DISTINCT EmpCode，' OUT' AS [状态] FROM InOut WHERE CAST（ActionDatetime AS 日期）= @ Day这给了我结果：
EmpCode状态
emp100 IN
emp102 IN
emp103 IN
emp100 OUT
emp102 OUT
emp103 OUT如果我然后查询这些结果，LEFT JOIN到表格我将获得Status = IN的每个员工至少一个条目以及每个员工的至少一个条目= OUT。我也将借此机会创建一个In and Out 列/ i>员工按日期划分每条记录
DECLARE @ Day DATE = ' 2017-05-18'
如果 OBJECT_ID （' tempdb .. #lift'） 不 null drop table #shifts
; 预期为
（
SELECT DISTINCT EmpCode，' IN' AS [状态] FROM InOut WHERE CAST（ActionDatetime AS 日期）= @ Day
UNION ALL
SELECT DISTINCT EmpCode，' OUT' AS [状态] FROM InOut WHERE CAST（ActionDatetime AS 日期） = @ Day
）
选择 Id，AE.EmpCode，AE。[状态]，ActionDatetime，
CASE WHEN AE。[状态] = ' IN' THEN ActionDatetime ELSE CAST（ NULL AS datetime ） END AS InTime，
CASE WHEN AE。[Status] = ' IN' 那么 CAST（ NULL AS datetime ） ELSE ActionDatetime END AS OutTime
，RANK（） OVER （ PARTITION BY AE.EmpCode，AE。[Status] ORDER BY AE.EmpCode，ActionD atetime）
into #shifts
FROM 预期AE
< span class =code-keyword> LEFT OUTER JOIN InOut actual ON AE.EmpCode = actual.EmpCode AND AE。[状态] =实际。[状态]
WHERE Id IS NOT NULL AND CAST（ActionDatetime AS 日期）= @ Day给我结果
Id EmpCode状态ActionDateTime InTime OutTime
1 emp100在2017-05-18 10:00 2017-05-18 10:00 NULL 1
2 emp100 OUT 2017-05-18 15:00 NULL 2017-05-18 15:00 1
3 emp100在2017-05-18 17:00 2017-05-18 17:00 NULL 2
4 emp100 OUT 2017-05-18 20:00 NULL 2017-05-18 20:00 2
5 emp102 IN 2017-05-18 06:00 2017-05-18 06:00 NULL 1
6 emp102 OUT 2017-05-18 10:00 NULL 2017-05-18 10:00 1
7 emp103 IN 2017-05-18 07:00 2017-05-18 07:00 NULL 1
8 emp103 IN 2017-05-18 10:00 2017-05-18 10:00 NULL 2
我们可以通过使用GROUP BY如果 OBJECT_ID （' tempdb ..＃shifting2'） 不 null drop table #lift2
SELECT EmpCode，rn，MIN（id）< span class =code-keyword> as Id，
MAX（InTime） as InTime，MAX（OutTime） AS OutTime
， CASE WHEN MAX（InTime） IS NULL 或 MAX（OutTime） IS NULL THEN ' 错误' ELSE ' ' END AS 注意
INTO #lift2
FROM #shifts
GROUP BY EmpCode，rn这给了我更加整洁的结果：
empCode和Id TimeIn TimeOut Notes
emp100 1 1 2017-05-18 10:00 2017-05-18 15:00
emp100 2 3 2017-05-18 17:00 2017-05-18 20 ：00
emp102 1 5 2017-05-18 06:00 2017-05-18 10:00
emp103 1 7 2017-05-18 07:00 NULL Error
emp103 2 8 2017 -05-18 10:00 NULL错误请注意，您可以忽略消息警告：聚合或其他SET操作消除了空值。
此时您可以选择PIVOT列TimeIn和TimeOut但我选择使用LEAD函数而不是; WITH res AS
（
SELECT rn，EmpCode，InTime AS InTime1，OutTime as OutTime1，
LEAD（InTime） OVER （ PARTITION BY EmpCode ORDER BY Id） AS InTime2，
LEAD（OutTime） OVER （ PARTITION BY EmpCode ORDER BY Id） AS OutTime2，
注释
FROM #lift2
）
SELECT EmpCode，InTime1， OutTime1，InTime2，OutTime2，
isnull（DATEDIFF（hh，InTime1，OutTime1）， 0 ）+ isnull（DATEDIFF（HH，InTime2，OutTime2），< span class =code-digit> 0 ） AS TotalHours
，Notes
FROM res
WHERE rn = 1结果：
emp100 2017-05-18 10:00 2017-05-18 15:00 2017-05-18 17:00 2017-05-18 20：0 0 8
emp102 2017-05-18 06:00 2017-05-18 10:00 NULL NULL 4
emp103 2017-05-18 07:00 NULL 2017-05-18 10:00 NULL 0错误的
Looking for stored procedure to calculate total work hours of the employees with multiple in&out
Table structure
Id - primary let
EmpCode - employee code
Status - IN/OUt
ActionDatetime - DateTime
Sample record
1 emp100 IN 2017-05-18 10:00
2 emp100 OUT 2017-05-18 15:00
3 emp100 IN 2017-05-18 17:00
4 emp100 OUT 2017-05-18 20:00
5 emp102 IN 2017-05-18 06:00
6 emp102 OUT 2017-05-18 10:00
7 emp103 IN 2017-05-18 07:00
8 emp103 IN 2017-05- 18 10:00
I have to sample data like above.
I want to run a stored procedure with date.
It should give the result like.
1.List of the employees codes without repeating of employees code ( employees code can't repeat in the rows)
2. All IN and Out for all employees like.
1. Emp100 IN 2017-05-18 10:00 OUT 2017-05-18 15:00 IN 2017-05-18 17:00 OUT 2017-05-18 20:00 (total hrs )
If any in or out missed for the employees ex, odd numbers of in and out. Also continues same sequence of same status in in also starting of the day with out without in it should show in the last columns as a remarks.
This job was posted from a mobile device, so please pardon any typos or any missing details.
What I have tried:
USE [test]
GO
/****** Object: StoredProcedure [dbo].[GetWorkingHours] Script Date: 19-05-2017 12:07:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkingHours]
@Day date
AS
BEGIN
SET NOCOUNT ON;
with EmpIn as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%IN%'
),
EmpOut as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%OUT%'
)
select
e1.EmpId as 'EmpId',
e1.Date as 'In',
e2.Date as 'Out',
datediff(MINUTE, e1.[Date], e2.[Date]) as 'TotalHours'
from EmpIn e1
full outer join EmpOut e2 on e1.RowNum = e2.RowNum
endWe do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.
So give it a try, and see how far you can get - if you meet a specific problem, then please ask about that and we will do our best to help. But we aren't going to do it all for you!
I had a go at this. As I said in my comment I hope this isn't homework as I found it quite difficult to get all of the information you wanted.
The first problem to deal with is the missing information. The best way to handle that is to generate a sequence of expected entries e.g.SELECT DISTINCT EmpCode, 'IN' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day UNION ALL SELECT DISTINCT EmpCode, 'OUT' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @DayThat gave me these results:
EmpCode Status emp100 IN emp102 IN emp103 IN emp100 OUT emp102 OUT emp103 OUTIf I then query against those results, LEFT JOINing to the table I will get at least one entry for every employee for Status = IN and at least one entry for every employee for Status = OUT. I'll also take that opportunity to create an In and Out column/i> and RANK each record by Employee by date
DECLARE @Day DATE = '2017-05-18' if OBJECT_ID('tempdb..#shifts') is not null drop table #shifts ;with Expected as ( SELECT DISTINCT EmpCode, 'IN' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day UNION ALL SELECT DISTINCT EmpCode, 'OUT' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day ) select Id, AE.EmpCode, AE.[Status],ActionDatetime, CASE WHEN AE.[Status] = 'IN' THEN ActionDatetime ELSE CAST(NULL AS datetime) END AS InTime, CASE WHEN AE.[Status] = 'IN' THEN CAST(NULL AS datetime) ELSE ActionDatetime END AS OutTime , RANK() OVER (PARTITION BY AE.EmpCode, AE.[Status] ORDER BY AE.EmpCode, ActionDatetime) rn into #shifts FROM Expected AE LEFT OUTER JOIN InOut actual ON AE.EmpCode = actual.EmpCode AND AE.[Status]=actual.[Status] WHERE Id IS NOT NULL AND CAST(ActionDatetime AS Date) = @Daygives me the results
Id EmpCode Status ActionDateTime InTime OutTime rn 1 emp100 IN 2017-05-18 10:00 2017-05-18 10:00 NULL 1 2 emp100 OUT 2017-05-18 15:00 NULL 2017-05-18 15:00 1 3 emp100 IN 2017-05-18 17:00 2017-05-18 17:00 NULL 2 4 emp100 OUT 2017-05-18 20:00 NULL 2017-05-18 20:00 2 5 emp102 IN 2017-05-18 06:00 2017-05-18 06:00 NULL 1 6 emp102 OUT 2017-05-18 10:00 NULL 2017-05-18 10:00 1 7 emp103 IN 2017-05-18 07:00 2017-05-18 07:00 NULL 1 8 emp103 IN 2017-05-18 10:00 2017-05-18 10:00 NULL 2
We can tidy those results up by using GROUP BYif OBJECT_ID('tempdb..#shifts2') is not null drop table #shifts2 SELECT EmpCode, rn, MIN(id) as Id, MAX(InTime) as InTime, MAX(OutTime) AS OutTime , CASE WHEN MAX(InTime) IS NULL or MAX(OutTime) IS NULL THEN 'Error' ELSE '' END AS Notes INTO #shifts2 FROM #shifts GROUP BY EmpCode, rnWhich gives me the much tidier results:
empCode rn Id TimeIn TimeOut Notes emp100 1 1 2017-05-18 10:00 2017-05-18 15:00 emp100 2 3 2017-05-18 17:00 2017-05-18 20:00 emp102 1 5 2017-05-18 06:00 2017-05-18 10:00 emp103 1 7 2017-05-18 07:00 NULL Error emp103 2 8 2017-05-18 10:00 NULL ErrorNote you can ignore the message "Warning: Null value is eliminated by an aggregate or other SET operation."
At this point you could choose to PIVOT the columns TimeIn and TimeOut but I chose to use the LEAD function instead;WITH res AS ( SELECT rn,EmpCode, InTime AS InTime1, OutTime as OutTime1, LEAD(InTime) OVER (PARTITION BY EmpCode ORDER BY Id) AS InTime2, LEAD(OutTime) OVER (PARTITION BY EmpCode ORDER BY Id) AS OutTime2, Notes FROM #shifts2 ) SELECT EmpCode,InTime1,OutTime1,InTime2,OutTime2, isnull(DATEDIFF(hh, InTime1,OutTime1),0) + isnull(DATEDIFF(HH, InTime2, OutTime2),0) AS TotalHours , Notes FROM res WHERE rn = 1Results:
emp100 2017-05-18 10:00 2017-05-18 15:00 2017-05-18 17:00 2017-05-18 20:00 8 emp102 2017-05-18 06:00 2017-05-18 10:00 NULL NULL 4 emp103 2017-05-18 07:00 NULL 2017-05-18 10:00 NULL 0 Error
这篇关于使用多个输入/输出计算总工时的存储过程。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
