CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Internet Engineer
Internet Engineer

Reputation: 2534

Find Duplicate Phones using cte by comparing against other groups

I am trying to determine how many duplicate work phones I can find in Group 94 by comparing against all other groups that are active.

The sql is working by setting rownumber for each duplicate Workphone > 1.

The problem is that is setting the rownumber > 1 for all groups except for 94. I need to know 94 first and foremost. Any idea how I set rownumber > 1 for duplicates in GroupId 94 first?

DECLARE @GroupID Int
SET @GroupID = 94   


;WITH cte AS
(
  SELECT ROW_NUMBER() OVER (PARTITION BY d.WorkPhone ORDER BY c.id DESC)
    AS rownumber
    ,d.WorkPhone
    ,c.id
    ,GroupID
    FROM ContactTable c
    INNER JOIN DetailedContactTable d
    ON c.DetailedContactId = d.id
    WHERE  c.GroupID IN 
    (
        SELECT id 
            FROM ContactSummaryTable WHERE id = @GroupID
            OR GroupActive = 1
    )
    AND NOT d.WorkPhone IS NULL
    AND d.WorkPhone <> ''
)
SELECT * FROM cte WHERE rownumber > 1 
ORDER BY GroupID;

Upvotes: 0

Views: 444

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

DECLARE @GroupID INT;
SET     @GroupID = 94;

WITH BaseGroup
AS
(
SELECT  c.GroupID
        ,d.WorkPhone
        ,c.id ContactID
FROM    ContactTable c
INNER JOIN DetailedContactTable d ON c.DetailedContactId = d.id
WHERE   c.GroupID = @GroupID
AND     NOT d.WorkPhone IS NULL
AND     d.WorkPhone <> ''
), 
OtherGroups
AS
(
SELECT  
        d.WorkPhone
FROM    ContactTable c
INNER JOIN DetailedContactTable d ON c.DetailedContactId = d.id
WHERE   c.GroupID <> @GroupID
AND     NOT d.WorkPhone IS NULL
AND     d.WorkPhone <> ''
AND     EXISTS (
            SELECT * 
            FROM ContactSummaryTable WHERE id = c.GroupID
            AND GroupActive = 1)
)
SELECT  a.*
FROM    BaseGroup a
INNER JOIN OtherGroups b ON a.WorkPhone = b.WorkPhone
ORDER BY a.WorkPhone

or

SELECT  a.*, CASE WHEN b.WorkPhone IS NULL THEN 'no duplicate' ELSE 'duplicate' END [Status]
FROM    BaseGroup a
LEFT JOIN OtherGroups b ON a.WorkPhone = b.WorkPhone
ORDER BY a.WorkPhone

Upvotes: 2

Related Questions