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

Sam 20 Reputation points
2023-03-27T10:13:19.0366667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,485 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 21,626 Reputation points Microsoft Vendor
    2023-03-28T01:47:31.1066667+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Muhammad Ahsan Khan 245 Reputation points
    2023-03-27T10:30:41.2033333+00:00

    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;