List No of minutes worked by an employee using self join

I have a SQL Server table emp_swipe_rec which has only 6 columns. I want to know how much time (in minutes) both employees A and B worked for today (last 24 hours). Employee A already swiped out for the day. Employee B is still working. We don’t store two different columns for a swipe in/swipe out. We use swipe_type (I – In/ O- Out) to determine the employee is still working or swipe out.

I need the two outputs from the query. Total Working Minutes of each Employee who swiped in last 24 hours. (Both swipe in/swipe out employees for the past 24 hours) List of employees who are working right now along with their total minutes of work till they swipe in. (Only swipe in and not swipe out for past 24 hours)

Employee_ID	Employee_Name	Swipe_Type	Swipe_In_Out_Time	Department	Emp_Swipe_Rec_PK
1	A	I	2021-03-07 08:00:00.000	MARKETING	1
2	B	I	2021-03-07 08:00:00.000	SALES	2
1	A	O	2021-03-07 15:00:00.000	MARKETING	3

I tried the below query but it says:

invalid column name for starttime and endtime param inside the DATEDIFF row.

I don’t know what I am doing wrong.

SELECT one.EMPLOYEE_ID,one.EMPLOYEE_NAME
       one.SWIPE_IN_OUT_TIME AS starttime,
       two.SWIPE_IN_OUT_TIME AS endtime,
       DATEDIFF(minute,starttime,endtime) AS diff
FROM emp_swipe_rec one
INNER JOIN emp_swipe_rec two ON two.SWIPE_TYPE <> one.SWIPE_TYPE
WHERE one.Employee_Id = two.Employee_Id AND one.Department = two.Department

Answer

I think this satisfies your first requirement “Total Working Minutes of each Employee who swiped in last 24 hours”

This returns one record for employee 1 as that is the only employee who has a sign out (in August 2021!?)

As this uses a window function, you will need to be using SQL Server 2012 or above, you have tagged both 2008 and 2012

;WITH SwipeIns AS
(
    SELECT  Employee_ID,
            Employee_Name,
            Swipe_In_Out_Time
    FROM    emp_swipe_rec 
    WHERE   Swipe_Type = 'I' AND
            Swipe_In_Out_Time > DATEADD(HOUR,-24,GETDATE()) AND
            Swipe_In_Out_Time < GETDATE()
),
SwipeOuts AS
(
    SELECT  Employee_ID,
            Swipe_In_Out_Time
    FROM    emp_swipe_rec 
    WHERE   Swipe_Type = 'O' AND
            Employee_ID IN (SELECT Employee_ID FROM SwipeIns) AND
            Swipe_In_Out_Time > DATEADD(HOUR,-24,GETDATE())
)

SELECT  Employee_ID,
        Employee_Name,
        SwipeIn,
        SwipeOut,
        DATEDIFF(MINUTE,SwipeIn,SwipeOut) AS Duration
FROM    (
            SELECT  i.Employee_ID,
                    i.Employee_Name,
                    i.Swipe_In_Out_Time AS SwipeIn,
                    o.Swipe_In_Out_Time AS SwipeOut,
                    ROW_NUMBER() OVER (PARTITION BY i.Employee_ID ORDER BY o.Swipe_In_Out_Time) AS RowN
            FROM    SwipeIns i
                    JOIN SwipeOuts o
                        ON i.Employee_ID = o.Employee_ID
        ) a
WHERE   RowN = 1

Also, why is your table not normalized? Do you not have an employees table? EmployeeName and Department are redundant