asked on

SQL Server possible dupe record

I need to search a table
UID IndividualID MessageText DateAdded

Where there are duplicate records on IndividualID and MessageText within 5 minutes of each other

Pawan Kumar

Can you please provide few input rows.

Larry Brister

ASKER

Hey Pawan...
Figured it out I think...

Can you review this ?

SELECT m1.MessageID,
       m2.MessageID,
       m2.IndividualID ,
       m2.MessageText ,
       m1.DateAdded ,
       m2.DateAdded
FROM   ewMessages m1
       INNER JOIN ewMessages m2 ON m2.IndividualID = m1.IndividualID
                                   AND m2.MessageText = m1.MessageText
WHERE  m1.SalesSiteID = 103887
       AND m1.MessageID <> m2.MessageID
       AND CAST(m1.DateAdded AS DATE) = CAST(m2.DateAdded AS DATE)
       --Change the difference in minutes to widen scope
       AND m2.DateAdded <= DATEADD(mi, 2, m1.DateAdded);

Open in new window

Example output is...

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Scott Pletcher

DECLARE @minute_range int
SET @minute_range = 2 /*adjust if/as needed*/

SELECT m1.MessageID,
m2.MessageID,
m2.IndividualID,
m2.MessageText,
m1.DateAdded,
m2.DateAdded
FROM ewMessages m1
INNER JOIN ewMessages m2 ON m2.SalesSiteID = 103887 /*optional, remove if not wanted*/
AND m2.MessageID > m1.MessageID
AND m2.IndividualID = m1.IndividualID
AND m2.MessageText = m1.MessageText
AND m2.DateAdded >= m1.DateAdded
AND m2.DateAdded <= DATEADD(MINUTE, @minute_range, m1.DateAdded)
WHERE m1.SalesSiteID = 103887

Larry Brister

ASKER

Sorry Scott
Saw it late but thanks

Larry Brister

ASKER

Thanks!