如何从具有所有刷卡记录的表中仅选择刷卡用户

问题描述：

我有一个只有 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

如何从具有所有刷卡记录的表中仅选择刷卡用户

相关推荐