让每个员工都打卡进出? [英] Get All punch in and out for each employee?

问题描述

预期输出:

SL#  Emp#   Employee Name       IN                     OUT         
1    106    George Jacob 24/Sep/2017 8:08:00AM 24/Sep/2017 12:53:00PM 04:45:00
                         24/Sep/2017 2:13:00PM 24/Sep/2017 5:58:00PM 03:45:00
                         25/Sep/2017 8:12:00AM 25/Sep/2017 6:02:00PM 09:50:00
                         26/Sep/2017 8:18:00AM 26/Sep/2017 2:15:00PM 05:57:00
                         26/Sep/2017 2:32:00PM 26/Sep/2017 6:00:00PM 03:28:00
                         27/Sep/2017 8:02:00AM 27/Sep/2017 5:57:00PM 09:55:00
                         28/Sep/2017 8:01:00AM 28/Sep/2017 6:01:00PM 10:00:00
                         01/Oct/2017 8:16:00AM 01/Oct/2017 5:56:00PM 09:40:00
                         02/Oct/2017 7:58:00AM 02/Oct/2017 5:56:00PM

我尝试了这个查询，但没有得到上面提到的确切输出:

I tried this query but not get an exact output as mentioned above:

SELECT Row_number()
     OVER (ORDER BY A.dt ASC)                        AS SNo,
     CONVERT(DATE, A.dt)
   --CONVERT(VARCHAR(26), A.DT, 103)  as DATEEVENT, 
                                                 b.emp_code,
   B.emp_name,
   F.event_entry_name,
   a.dt,
   Cast(LEFT(CONVERT(TIME, a.dt), 5) AS VARCHAR) AS 'time',
   Isnull(B.areaname, 'OAE6080036073000006')     AS areaname,
   C.dept_name,
   b.emp_reader_id,
   Isnull(c.dept_name, '')                       AS group_name,
   CONVERT(CHAR(11), '2017/12/30', 103)          AS StartDate,
   CONVERT(CHAR(11), '2018/01/11', 103)          AS ToDate,
   0                                             AS emp_card_no
FROM   dbo.trnevents AS A
   LEFT OUTER JOIN dbo.employee AS B
                ON A.emp_reader_id = B.emp_reader_id
   LEFT OUTER JOIN dbo.departments AS C
                ON B.dept_id = C.dept_id
   LEFT OUTER JOIN dbo.devicepersonnelarea AS E
                ON A.pointid = E.areaid
   LEFT OUTER JOIN dbo.event_entry AS F
                ON A.eventid = F.event_entry_id  

推荐答案

您可以使用子选择或 CTE 来获取按员工排序的数据并将其用作主数据表.与此类似的内容(如有必要，请对其进行调整):

You could use a subselect or CTE to get the data ordered by employee and the use it as the main data table. Something similar (adapt it if necessary) to this:

;with ordered as (
select 
    emp_reader_id as empId,
    CONVERT(DATE, dt) as Punch,
    Row_number()
     OVER (PARTITION BY emp_reader_id ORDER BY CONVERT(DATE, dt) ASC) as OrderedPunch
from trnevents
)
SELECT 
    entered.empId, 
    entered.Punch as PunchIn,
    exited.Punch as PunchOut
from
    ordered as entered
    left join ordered as exited on 
        entered.empId = exited.empId
        and entered.OrderedPunch + 1 = exited.OrderedPunch

说明:有序"CTE 确实显示了按日期排序的员工进入/退出.由于 PARTITION BY，为每个员工重置 ROW_NUMBER(我假设 emp_reader_id 确实包含员工 ID).

Explanation: The 'ordered' CTE does show the employee entry/exits ordered by date. The ROW_NUMBER is reset for each employee (I assume the emp_reader_id does contains the employee id) because of the PARTITION BY.

获得每个员工的计数器后，我将每个员工的每次打卡(左连接中的第一个条件)与该员工的下一个打卡(左连接中的第二个条件)连接起来.这样我就可以显示入口栏和出口(下一个冲床).

Once I got the counter for each employee, I join each punch for each employee (first condition in the left join) with the next punch for that employee (second condition in the left join). That way I can show the entry column and the exit (the next punch).

在获得数据中的进出列后，您可能想要排除一些数据(每个员工的奇数行是您想要的行)添加 WHERE 输入.OrderedPunch %2 = 1

After you got the in and out columns in your data you may want to exclude some data (the odd rows of each employee are the rows you would want) adding WHERE entered.OrderedPunch %2 = 1

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