SSMS- I want to pickup only first record and remove duplicate values from the column

Question

I am using SQL Server 2019, I have a table which has attendance data.

In this attendance column there are multiple attendance entries for the same employee ID but I want to pickup only first entry when he swiped in and remaining entries I want to delete other entries.

ex-
2023-03-23 21:07:47.000

2023-03-23 02:27:38.000

from above column (datetime data type) I want only '2023-03-23 02:27:38.000' and i want to delete other entries.

This is not static.

Answer 1

Hi @Sam

we can see same user for same day he logged in 2 times, but I want to consider only 2nd row because it was old and first logged in by user.

If I understand correctly, you want to delete one user's record within the same day except for the first login.

Please check this sample:

CREATE TABLE #TEST (ID VARCHAR(20),Name VARCHAR(20),Store_Name VARCHAR(20),Login_Hrs decimal(6,2),first_checkin DATETIME)
INSERT INTO #TEST VALUES
('AB409123','ABC','XYZ',0.02,'3/23/2023 21:07'),
('AB409123','ABC','XYZ',0,'3/23/2023 2:27'),
('AB409123','ABC','XYZ',4.55,'3/17/2023 11:09'),
('AB409123','ABC','XYZ',7.55,'3/16/2023 11:48'),
('AB409124','CBA','XXA',0.67,'3/15/2023 11:25'),
('AB409125','CBD','XXA',0,'3/13/2023 11:10')

;WITH CTE AS 
(
 SELECT *,ROW_NUMBER() OVER (PARTITION BY ID,CONVERT(DATE,first_checkin) ORDER BY first_checkin) AS RowNum           
 FROM #TEST
)
SELECT * FROM CTE --if the result is right, then comment this line and uncomment the DELETE line below.
--DELETE FROM CTE WHERE RowNum > 1;

DROP TABLE #TEST

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can use a subquery to select only the first entry for each employee ID and then delete all other entries from the table. Here's an example SQL query that should achieve this:

This query uses a Common Table Expression (CTE) to assign a row number to each attendance entry for each employee ID, based on the order of their attendance date and time. The PARTITION BY clause groups the rows by EmployeeID and the ORDER BY clause orders the rows by AttendanceDateTime.

The DELETE statement then removes all rows where the row number is greater than 1, effectively deleting all duplicate attendance entries for each employee ID except for the first one.

WITH cte AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY AttendanceDateTime) AS RowNum,
           AttendanceDateTime
    FROM AttendanceTable
)
DELETE FROM cte
WHERE RowNum > 1;