如何从具有所有刷卡记录的表中仅选择刷卡用户 [英] How do select only swipe-in users from the table which has all swipe records

问题描述

我有一个只有 6 列的 SQL Server 表 task_swipe_rec.我想知道现在有多少人为分配的任务刷卡，以及他们目前在这些任务上花费的时间列表.

I have a SQL Server table task_swipe_rec which has only 6 columns. I want to know how many people swipe in right now for the assigned task and the list of minutes they spent on those tasks till now.

我希望获得当前刷入的员工未刷出(未完成)的任务列表以及每项任务花费的分钟数.

I am expecting to get the list of tasks that are not swiped out (not completed) and number of minutes spent on each task by the employee who swiped in currently.

<头>

Employee_ID	Task_Id	Swipe_Type	Swipe_In_Out_Time	部门	Emp_Swipe_Rec_PK
EMP1	TASK1	我	2021-03-07 08:00:00.000	营销	1
EMP2	TASK2	我	2021-03-07 08:00:00.000	销售	2
EMP1	TASK1	O	2021-03-07 15:00:00.000	营销	3
EMP1	TASK3	我	2021-03-07 16:00:00.000	营销	4

SELECT
    one.EMPLOYEE_ID,
    one.TASK_ID
    DATEDIFF( minute, one.SWIPE_IN_OUT_TIME, GETDATE() ) AS TimeSpent,   
FROM
    emp_swipe_rec one
    INNER JOIN emp_swipe_rec two ON two.SWIPE_TYPE = 'I'
WHERE
    one.Employee_Id = two.Employee_Id
    AND
    one.TASK_ID = two.TASK_ID

预期结果:

<头>

Employee_ID	Task_Id	TimeSpent
EMP2	TASK2	130
EMP1	TASK3	40

推荐答案

我认为您需要如下逻辑，您可以检查每个打开记录是否存在关闭记录.但是，使用提供的数据似乎无法获得预期的结果.

I think you want logic like the following, where you check whether there exists a closing record for every opening record. However it doesn't seem possible to get your expected results using the data provided.

DECLARE @emp_swipe_rec table (EMPLOYEE_ID int, TASK_ID int, SWIPE_TYPE char(1), SWIPE_IN_OUT_TIME datetime2);

DECLARE @Now datetime2 = '2021-03-07 16:40:00.000'; --sysdatetime();

INSERT INTO @emp_swipe_rec (EMPLOYEE_ID, TASK_ID, SWIPE_TYPE, SWIPE_IN_OUT_TIME)
VALUES
(1,1,'I','2021-03-07 08:00:00.000'),
(2,2,'I','2021-03-07 08:00:00.000'),
(1,1,'O','2021-03-07 15:00:00.000'),
(1,3,'I','2021-03-07 16:00:00.000');

SELECT I.EMPLOYEE_ID, I.TASK_ID
  , DATEDIFF(MINUTE, I.SWIPE_IN_OUT_TIME, @Now) AS TimeSpent
FROM @emp_swipe_rec I
WHERE SWIPE_TYPE = 'I'
-- Rule out any where a closing record in the future exists
AND NOT EXISTS (
  SELECT 1
  FROM @emp_swipe_rec O
  WHERE O.EMPLOYEE_ID = I.EMPLOYEE_ID
  AND O.TASK_ID = I.TASK_ID
  AND SWIPE_TYPE = 'O'
  AND O.SWIPE_IN_OUT_TIME > I.SWIPE_IN_OUT_TIME
);

使用提供 40 分钟的时间戳返回以下内容 - 但不确定您是如何想到 130 分钟的:

Returns the following using a timestamp which gives 40mins - but unsure how you came up with 130min:

<头>

EMPLOYEE_ID	TASK_ID	花费的时间
2	2	520
1	3	40

请注意以供将来参考，如果您以这种方式 (DDL+DML) 提供示例数据，不仅可以让您的问题更清晰，而且可以让人们更容易回答

Please note for future reference, if you provide you sample data this way (DDL+DML) you not only make your question clearer, you also make it much easier for people to answer

这篇关于如何从具有所有刷卡记录的表中仅选择刷卡用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！