问题描述
我的表格如下:
id time_stamp evenid
1001 2017-09-05 09:35:00 1(in)
1002 2017-09-05 11:00:00 0(out)
我使用了下面的查询，它得到了但不是一个确切的答案.错误在所需日期取第二天
I used below query, it gets but not an exact answer.Wrong taking next day in desired date
;WITH InOut (empId, EntryTime, ExitTime,POINTID,EVENTID,Belongs_to) as
(SELECT emp_reader_id ,
a1.DT ,
( SELECT MIN(a2.DT)
FROM trnevents a2
WHERE a1.emp_reader_id = a2.emp_reader_id
AND a1.DT < a2.DT
),a1.DeviceSerialNumber,EVENTID,Belongs_to
FROM trnevents a1
)
SELECT empId ,
EntryTime ,
ExitTime,EVENTID, Belongs_to
,CONVERT( CHAR(11), '2017/12/30', 103 ) as StartDate
,CONVERT( CHAR(11), '2018/01/15', 103 ) as ToDate
,case when convert(time,dateadd(ms,DATEDIFF(SECOND, EntryTime, ExitTime)*1000,0),114) is not null
then convert(time,dateadd(ms,DATEDIFF(SECOND, EntryTime, ExitTime)*1000,0),114) else '00:00' end as Worked FROM InOut A
错误的输出:它也在第二天得到
Wrong Output: it get next day also
EMP_NO INTIME OUTTIME DT WORKED
101 2018-01-08 09:11:14.000 2018-01-08 12:07:22.000 2018-01-08 02:56:08.0000000
101 2018-01-08 12:07:22.000 2018-01-08 14:35:33.000 2018-01-08 02:28:11.0000000
101 2018-01-08 14:35:33.000 **2018-01-09 09:14:00.000** 2018-01-08 18:38:27.0000000
101 2018-01-09 09:14:00.000 2018-01-09 14:28:23.000 2018-01-09 05:14:23.0000000
我想要的输出:
SL# Emp# Employee Name IN OUT Worked
2 107 Nisham M 24/Sep/2017 8:34:00AM 24/Sep/2017 10:15:00AM 01:41:00
24/Sep/2017 10:19:00AM 24/Sep/2017 12:20:00PM 02:01:00
24/Sep/2017 12:25:00PM 24/Sep/2017 12:54:00PM 00:29:00
24/Sep/2017 2:13:00PM 24/Sep/2017 2:14:00PM 00:01:00
24/Sep/2017 2:19:00PM 24/Sep/2017 5:25:00PM 03:06:00
10/Oct/2017 2:00:00PM 10/Oct/2017 2:52:00PM 00:52:00
10/Oct/2017 3:15:00PM NULL -
11/Oct/2017 8:26:00AM 11/Oct/2017 10:29:00AM 02:03:00
19/Oct/2017 1:59:00PM 19/Oct/2017 5:56:00PM 03:57:00
Total Shift Hours Total Basic Hours OT Normal Hours Total Hours Worked
210 159:20:00 00:00:00 159:20:00
我被困在这里并搜索了最多但没有得到任何人帮助的所需输出
i stuck here and search most but not getting desired output anyone help
推荐答案
根据这个答案，补充:>
According to this answer, adding:
AND DATEDIFF(DAY, a1.DT, a2.DT) = 0
WHERE 子句应该确保开始日期和结束日期在同一天，但是如果有人工作到午夜之后会发生什么?此外，您的帖子表明您也将结束时间作为开始时间，因此您可以:
To the WHERE clause should ensure the start and end date are on the same day, but what happens if someone works past midnight? Also your post suggests that you are picking up end times as start times also, so instead you could have:
AND a1.EVENTID = 1 AND a2.EVENTID = 0
如果两者都不合适，请进一步描述您的问题并提供更多示例数据.
If neither are suitable, please describe your problem further and provide some more sample data.
这篇关于让所有员工都打卡和打卡?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！