让所有员工都打卡和打卡? [英] Getting all punch in and punch out of an employee?

问题描述

我的表格如下:

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.

这篇关于让所有员工都打卡和打卡?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！