我希望得到旋转移位输出结果 [英] I want to get rotational shift in-out result
问题描述
我希望获得旋转班次输入结果
大家好，
需要帮助以下。
我想获得旋转班次输入结果。
我的表格如下：
MachinePunchingData
sEnrollNumber iDateTime
3 2018-04-03 07：11：10.000
3 2018-04-03 23：11：19.000
3 2018-04-03 23：40：29.000
3 2018-04-04 00：11：32.000
3 2018-04-04 01：11：34.000
3 2018-04-04 05：12：09.000
3 2018-04-04 07：10：26.000
3 2018-04-04 23：04：28.000
ShiftMaster
ShiftID ShiftStartTime ShiftEndTime TotalTime I sRotational
1 23:00:00 07:00:00 08:00:00 true
EmpMaster
EmpID PunchID（sEnrollNumber）Fname Lname IsActive ShiftID
1 3 Xyz Xyz true 1
我想得到以下结果：
sEnrollNumber iDateTime输入/输出AttnDate
3 2018-04-03 07：11：10.000输出2018-04-02
3 2018-04-03 23： 11：19.000在2018-04-03
3 2018-04-03 23：40：29.000 Out 2018-04-03
3 2018-04-04 00： 11：32.000在2018-04-03
3 2018-04-04 01：11：34.000 Out 2018-04-03
3 2018-04-04 05：12：09.000 2018-04-03
3 2018-04-04 07：10：26.000 Out 2018-04-03
3 2018-04-04 23：04：28.000在2018-04-04
问题：
我将如何识别哪一个用于哪一个用哪一个用于Out。
Emplolyee可能会提前到他的班次时间22:00:00
可能会在06:00:00去
员工可能会迟到：00：11：01
晚点：08：11：23
如果您对此有任何想法或解决方案请求大家帮助我。
谢谢，
Chintan
我尝试过：
选择
ROW_NUMBER（）OVER（分区BY [sEnrollNumber] ORDER BY [iDateTime] ASC）[PunchNum]，sEnrollNumber，
iDatetime，
将（iDatetime作为日期）转换为date2，
Cast（[iDateTime]为时间）> Cast（（从shiftMaster中选择dateadd（mi，-60，StartTime），其中ShiftID =（从EmployeeMaster中选择ShiftID1，其中PunchID = sEnrollNumber））作为时间）
和Cast（[iDateTime] as time）< ;转换（（从shiftMaster中选择dateadd（mi，59，StartTime），其中ShiftID =（从EmployeeMaster中选择ShiftID1，其中PunchID = sEnrollNumber））作为时间）
和（（ROW_NUMBER（）OVER（分区BY [ sEnrollNumber] ORDER BY [iDateTime] ASC））％2）= 0
然后'IN'否则'OUT'结束为'进/出'来自
PunchingData
其中[sEnrollNumber] = 3和'2018-04-01'和'2018-04-30'之间的iDateTime
你有正确的想法使用ROW_NUMBER和模数（％）你只是略微过于复杂的事情。
这个查询以整齐的格式获取数据选择 E.PunchID，iDateTime，
CASE WHEN （（ ROW_NUMBER（） OVER （分区 BY [ sEnrollNumber] ORDER BY [iDateTime] ASC ））％2）= 0 THEN ' IN' ELSE ' OUT' END as InOut，
Cast（iDatetime as date ） as date2，ShiftId
from #EmpMaster E
inner join #MachinePunchingData M ON E.PunchID = M.sEnrollNumber
ORDER BY iDateTime注意我暂时删除了Punch Number和Dates上的检查以使事情更清楚，但包括转移ID（为什么会变得明显）结果：
3 2018-04-03 07:11:10。 000 OUT 2018-04-03 1
3 2018-04-03 23:11:19。 000 IN 2018-04-03 1
3 2018-04-03 23:40:29。 000 OUT 2018-04-03 1
3 2018-04 -04 00:11:32。 000 IN 2018-04-04 1
3 2018-04-04 01:11:34。 000 OUT 2018-04-04 1
3 2018-04-04 05： 12:09。 000 IN 2018-04-041
3 2018-04-04 07:10:26。 000 OUT 2018-04-04 1
3 2018 -04-04 23:04:28。 000 IN 2018-04-04 1现在是时候查看您的数据......员工在轮班期间似乎多次进出工作场所！你可能想先从一些简单的数据开始 - 每个班次只有一个条目和一个出口。
然而，坚持我们所拥有的，你会想要的知道他们什么时候进来，他们出去的时间和工作时间，最好是在同一排。您可以使用之一SQL分析函数 [ ^ ]这样做。我将使用LEAD获取下一个时间选择 E.PunchID，
idatetime as time1，
LEAD（iDatetime， 1 ） OVER （ partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC ） as time2，
CASE WHEN （（ROW_NUMBER（） OVER （ partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC ））％2）= 0 那么 ' IN' ELSE ' OUT' END as InOut，
Cast（iDatetime as date ） as date2
，E.ShiftID
from #EmpMaster E
inner join #MachinePunchingData M ON E.PunchID = M.sEnrollNumber
结果：
3 2018-04-03 07：11：10.000 2018-04-03 23：11：19.000 OUT 2018-04-03 1
3 2018-04-03 23：11：19.000 2018-04-03 23：40：29.000 IN 2018-04-03 1
3 2018-04-03 23：40：29.000 2018-04- 04 00：11：32.000 OUT 2018-04-03 1
3 2018-04-04 00：11：32.000 2018-04-04 01：11：34.000 IN 2018-04-04 1
3 2018-04-04 01：11：34.000 2018-0 4-04 05：12：09.000 OUT 2018-04-04 1
3 2018-04-04 05：12：09.000 2018-04-04 07：10：26.000 IN 2018-04-04 1
3 2018-04-04 07：10：26.000 2018-04-04 23：04：28.000 OUT 2018-04-04 1
3 2018-04-04 23：04：28.000 NULL在2018-04- 04 1请注意我们没有他离开最后一班的时间！
你想要扩展这个基础数据能够从中获取真实信息，并注意到你真的只对IN行感兴趣（OUT行实际显示员工不的时间长度工作中）。为此，我建议您使用公用表表达式（CTE）...它有助于使复杂查询更清晰，这意味着您可以逐步构建查询，随时检查结果; cte AS
（
选择 E.PunchID，
idatetime as time1，
LEAD（iDatetime， 1 ） OVER （ partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC ） as time2，
CASE WHEN （（ROW_NUMBER（） OVER （ partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC ））％2）= 0 那么 ' IN' ELSE ' OUT' END as InOut，
Cast（iDatetime as date ） as date2
，E.ShiftID
来自 #EmpMaster E
内部 join #MachinePunchingData M ON E.PunchID = M.sEnrollNumber
）
SELECT PunchID，time1，time2，date2
来自 cte
WHERE InOut = ' IN '
ORDER BY time1结果：< pre lang =text> 3 2018-04-03 23：11：19.000 2018-04-03 23：40：29.000 2018-04-03
3 2018-04-04 00：11：32.000 2018- 04-04 01：11：34.000 2018-04-04
3 2018-04-04 05：12：09.000 2018-04-04 07：10：26.000 2018-04-04
3 2018- 04-04 23：04：28.000 NULL 2018-04-04这应该会让你持续一段时间。
I want to get rotational shift In-Out result
Hello Everyone,
Need Help for below.
I want to get rotational shift In-Out result.
My Table is as below :
MachinePunchingData
sEnrollNumber iDateTime
3 2018-04-03 07:11:10.000
3 2018-04-03 23:11:19.000
3 2018-04-03 23:40:29.000
3 2018-04-04 00:11:32.000
3 2018-04-04 01:11:34.000
3 2018-04-04 05:12:09.000
3 2018-04-04 07:10:26.000
3 2018-04-04 23:04:28.000
ShiftMaster
ShiftID ShiftStartTime ShiftEndTime TotalTime IsRotational
1 23:00:00 07:00:00 08:00:00 true
EmpMaster
EmpID PunchID(sEnrollNumber) Fname Lname IsActive ShiftID
1 3 Xyz Xyz true 1
I want below result:
sEnrollNumber iDateTime In/Out AttnDate
3 2018-04-03 07:11:10.000 Out 2018-04-02
3 2018-04-03 23:11:19.000 In 2018-04-03
3 2018-04-03 23:40:29.000 Out 2018-04-03
3 2018-04-04 00:11:32.000 In 2018-04-03
3 2018-04-04 01:11:34.000 Out 2018-04-03
3 2018-04-04 05:12:09.000 In 2018-04-03
3 2018-04-04 07:10:26.000 Out 2018-04-03
3 2018-04-04 23:04:28.000 In 2018-04-04
Problems :
How i will identify which one for In and Which one for Out.
Emplolyee may be come earlier then his shift time like 22:00:00
And may be go at 06:00:00
Employee may be come late : 00:11:01
Go late : 08:11:23
Request you all if you get any idea or solution for this then help me.
Thanks,
Chintan
What I have tried:
select
ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC) [PunchNum],sEnrollNumber,
iDatetime,
Cast(iDatetime as date) as date2,
Case when
Cast([iDateTime] as time) > Cast((select dateadd(mi,-60,StartTime) from shiftMaster where ShiftID=(select ShiftID1 from EmployeeMaster where PunchID=sEnrollNumber)) as time)
and Cast([iDateTime] as time) < Cast((select dateadd(mi,59,StartTime) from shiftMaster where ShiftID=(select ShiftID1 from EmployeeMaster where PunchID=sEnrollNumber)) as time)
and ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2)=0
then 'IN' Else 'OUT' End as 'In/Out' from
PunchingData
where [sEnrollNumber]=3 and iDateTime between '2018-04-01' and '2018-04-30'
You had the right idea using ROW_NUMBER and modulus (%) you were just slightly over-complicating things.
This query gets the data in a tidy formatselect E.PunchID, iDateTime, CASE WHEN ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2) = 0 THEN 'IN' ELSE 'OUT' END as InOut, Cast(iDatetime as date) as date2, ShiftId from #EmpMaster E inner join #MachinePunchingData M ON E.PunchID=M.sEnrollNumber ORDER BY iDateTimeNote I've removed the checks on Punch Number and Dates for now to make things clearer, but included the Shift Id (why will become apparent) Result:
3 2018-04-03 07:11:10.000 OUT 2018-04-03 1 3 2018-04-03 23:11:19.000 IN 2018-04-03 1 3 2018-04-03 23:40:29.000 OUT 2018-04-03 1 3 2018-04-04 00:11:32.000 IN 2018-04-04 1 3 2018-04-04 01:11:34.000 OUT 2018-04-04 1 3 2018-04-04 05:12:09.000 IN 2018-04-04 1 3 2018-04-04 07:10:26.000 OUT 2018-04-04 1 3 2018-04-04 23:04:28.000 IN 2018-04-04 1Now is the time to look at your data ... that employee seems to be in and out of the workplace many times during their shift! You might want to start with some simpler data first - exactly one entry and exactly one exit per shift.
However, sticking with what we have, you are going to want to know what time they came in, what time they went out and how long they worked, preferably all on the same row. You can use one of the SQL Analytic Functions [^] to do that. I'm going to use LEAD to get the "next" timeselect E.PunchID, idatetime as time1, LEAD(iDatetime,1) OVER (partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC) as time2, CASE WHEN ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2) = 0 THEN 'IN' ELSE 'OUT' END as InOut, Cast(iDatetime as date) as date2 ,E.ShiftID from #EmpMaster E inner join #MachinePunchingData M ON E.PunchID=M.sEnrollNumberResults:
3 2018-04-03 07:11:10.000 2018-04-03 23:11:19.000 OUT 2018-04-03 1 3 2018-04-03 23:11:19.000 2018-04-03 23:40:29.000 IN 2018-04-03 1 3 2018-04-03 23:40:29.000 2018-04-04 00:11:32.000 OUT 2018-04-03 1 3 2018-04-04 00:11:32.000 2018-04-04 01:11:34.000 IN 2018-04-04 1 3 2018-04-04 01:11:34.000 2018-04-04 05:12:09.000 OUT 2018-04-04 1 3 2018-04-04 05:12:09.000 2018-04-04 07:10:26.000 IN 2018-04-04 1 3 2018-04-04 07:10:26.000 2018-04-04 23:04:28.000 OUT 2018-04-04 1 3 2018-04-04 23:04:28.000 NULL IN 2018-04-04 1Notice we don't have what time he left that last shift!
You're going to want to expand on this basic data to be able to get real information out of it, and notice that you're really only interested in the rows that are "IN" (the "OUT" rows actually show how long the employee was not in work). For that I'm going to suggest you use a Common Table Expression (CTE) ... it helps to make complex queries clearer and means you can build up the query in steps, checking your results as you go;with cte AS ( select E.PunchID, idatetime as time1, LEAD(iDatetime,1) OVER (partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC) as time2, CASE WHEN ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2) = 0 THEN 'IN' ELSE 'OUT' END as InOut, Cast(iDatetime as date) as date2 ,E.ShiftID from #EmpMaster E inner join #MachinePunchingData M ON E.PunchID=M.sEnrollNumber ) SELECT PunchID, time1,time2, date2 from cte WHERE InOut = 'IN' ORDER BY time1Results:
3 2018-04-03 23:11:19.000 2018-04-03 23:40:29.000 2018-04-03 3 2018-04-04 00:11:32.000 2018-04-04 01:11:34.000 2018-04-04 3 2018-04-04 05:12:09.000 2018-04-04 07:10:26.000 2018-04-04 3 2018-04-04 23:04:28.000 NULL 2018-04-04That should keep you going for a while.
这篇关于我希望得到旋转移位输出结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
