How do select only swipe-in users from the table which has all swipe records

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	Department	Emp_Swipe_Rec_PK
EMP1	TASK1	I	2021-03-07 08:00:00.000	MARKETING	1
EMP2	TASK2	I	2021-03-07 08:00:00.000	SALES	2
EMP1	TASK1	O	2021-03-07 15:00:00.000	MARKETING	3
EMP1	TASK3	I	2021-03-07 16:00:00.000	MARKETING	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

Expected Result :

Employee_ID	Task_Id	TimeSpent
EMP2	TASK2	130
EMP1	TASK3	40

Answer

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
);

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

EMPLOYEE_ID	TASK_ID	TimeSpent
2	2	520
1	3	40

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