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
Larry Bristersr. DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Can you please provide few input rows.
Larry Bristersr. DeveloperAuthor Commented:
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...

Screen Print
Pawan KumarDatabase ExpertCommented:
Looks ok to me..few modifications.. Have you verified the data?

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) /*This is not required*/
       --Change the difference in minutes to widen scope
       AND DATEDIFF(mi,m1.DateAdded,m2.DateAdded) <= 5  /* The time difference should be 5 mins*/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
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 Bristersr. DeveloperAuthor Commented:
Sorry Scott
Saw it late but thanks
Larry Bristersr. DeveloperAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.