CODEWITHSUNDEEP
sql-server
bluray
bluray

Reputation: 1963

SQL Server : deleting records with similar time

I would like to delete records that have same (or similar) time. This is the sample data:

ID | Name | DateTime
---+------+--------------------
1  | Joe  | 2017-03-01 11:33:13
1  | Joe  | 2017-03-01 11:33:14
1  | Joe  | 2017-03-01 11:33:15
1  | Joe  | 2017-03-01 11:55:30
2  | John | 2017-02-15 08:55:48
2  | John | 2017-02-15 08:55:49
2  | John | 2017-02-15 08:56:30
2  | John | 2017-02-15 10:15:40

After deleting:

ID | Name | DateTime
---+------+---------------------
1  | Joe  | 2017-03-01 11:33:13
1  | Joe  | 2017-03-01 11:55:30
2  | John | 2017-02-15 08:55:48
2  | John | 2017-02-15 10:15:40

Deletes all similar times (e.g. 10 minutes) for users

Please help me how to do it. Thanks

Upvotes: 2

Views: 40

Answers (1)

Chanukya
Chanukya

Reputation: 5893

CREATE TABLE #TABLE1
    ([ID] INT, [NAME] VARCHAR(4), [DATETIME] DATETIME)

INSERT INTO #TABLE1
    ([ID], [NAME], [DATETIME])
VALUES
    (1, 'JOE', '2017-03-01 11:33:13'),
    (1, 'JOE', '2017-03-01 11:33:14'),
    (1, 'JOE', '2017-03-01 11:33:15'),
    (1, 'JOE', '2017-03-01 11:55:30'),
    (2, 'JOHN', '2017-02-15 08:55:48'),
    (2, 'JOHN', '2017-02-15 08:55:49'),
    (2, 'JOHN', '2017-02-15 08:56:30'),
    (2, 'JOHN', '2017-02-15 10:15:40')

SELECT  A.ID,A.NAME,A.[DATETIME] FROM  
(SELECT *,ROW_NUMBER() OVER( PARTITION BY (  CAST(CONVERT(CHAR(16), [DATETIME],113) AS DATETIME)) ORDER BY [NAME]) AS RN FROM #TABLE1
)A
WHERE RN=1 ORDER BY ID

output

ID  NAME        DATETIME
1   Joe        2017-03-01 11:33:13.000
1   Joe        2017-03-01 11:55:30.000
2   John       2017-02-15 08:55:48.000
2   John       2017-02-15 10:15:40.000

Upvotes: 3

Related Questions