none
Help in Duplicate records query RRS feed

  • Question

  • Hello - I want to get those record which have duplicate Bid,Fname,Lname,DOB,REL,sex but have different status. 

    If records are duplicate (Bid,Fname,Lname,REL,Sex) and status also same then should not pick in the results.

    Create table #Dup (Bid char(4), seq_no integer, Fname char(10), Lname char(10), dob datetime, Rel char(1), sex char(1), status char(4))
    Insert into #Dup values ('1222',1,'SAM','HAM','2016-01-01','C','M','ACT')
    Insert into #Dup values ('1222',2,'SAM','HAM','2016-01-01','C','M','TERM')
    Insert into #Dup values ('1222',3,'SAM','HAM','2016-01-01','C','M','TERM')

    Insert into #Dup values ('1222',4,'Genn','Suhan','2018-02-01','C','M','ACT')

    Insert into #Dup values ('1224',1,'Lara','Sana','2015-04-01','C','M','ACT')
    Insert into #Dup values ('1224',2,'Lara','Sana','2015-04-01','C','M','TERM')

    Insert into #Dup values ('1224',3,'Junaid','G','2014-04-01','C','M','ACT')


    Insert into #Dup values ('1225',1,'Smith','F','2012-04-01','C','F','TERM')
    Insert into #Dup values ('1225',2,'Smith','F','2012-04-01','C','F','TERM')

    Insert into #Dup values ('1225',3,'Roma','Dude','2015-04-01','C','M','ACT')

    Select * from #Dup
    Bid seq_no Fname Lname dob Rel sex status
    1222 1 SAM        HAM     2016-01-01  C M ACT 
    1222 2 SAM        HAM     2016-01-01  C M TERM
    1222 3 SAM        HAM     2016-01-01  C M TERM
    1224 1 Lara      Sana    2015-04-01  C M ACT 
    1224 2 Lara      Sana    2015-04-01  C M TERM

    Friday, June 14, 2019 3:59 PM

Answers

  • This is how it can be done...

    SELECT Bid,
    	Fname,
    	Lname,
    	REL,
    	Sex
    FROM #Dup
    GROUP BY Bid,
    	Fname,
    	Lname,
    	REL,
    	Sex
    HAVING count(*) > 1
    	AND count(DISTINCT STATUS) > 1;

    Output:

    Bid  Fname      Lname      REL  Sex
    ---- ---------- ---------- ---- ----
    1222 SAM        HAM        C    M
    1224 Lara       Sana       C    M

    • Marked as answer by SmithDy Friday, June 14, 2019 5:53 PM
    Friday, June 14, 2019 4:26 PM
  • ;WITH CTE_1 AS (
    	SELECT Bid, Fname, Lname, Rel, sex, COUNT(*) AS counts
    	FROM #Dup
    	GROUP BY Bid, Fname, Lname, Rel, sex
    	HAVING COUNT(*) > 1
    ),
    CTE_2 AS (
    	SELECT Bid, Fname, Lname, Rel, sex, status, COUNT(*) AS counts
    	FROM #Dup
    	GROUP BY Bid, Fname, Lname, Rel, sex, status
    	HAVING COUNT(*) > 1
    ),
    CTE_3 AS (
    	SELECT c1.Bid, c1.Fname, c1.Lname, c1.Rel, c1.sex
    	FROM CTE_1 AS c1 
    	LEFT JOIN CTE_2 AS c2 ON c2.Bid = c1.Bid AND c2.Fname = c1.Fname AND c2.Lname = c1.Lname AND c2.Rel = c1.Rel AND c2.sex = c1.sex
    	WHERE c2.counts <> c1.counts OR c2.Bid IS NULL
    )
    
    SELECT d.*
    FROM #Dup AS d
    WHERE EXISTS (SELECT 1 FROM CTE_3 WHERE Bid = d.Bid AND Fname = d.Fname AND Lname = d.Lname AND Rel = d.Rel AND sex = d.sex)


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by SmithDy Friday, June 14, 2019 5:54 PM
    Friday, June 14, 2019 4:30 PM
  • Create table #Dup (Bid char(4), seq_no integer, Fname char(10), Lname char(10), 
    dob datetime, Rel char(1), sex char(1), status char(4))
    Insert into #Dup values ('1222',1,'SAM','HAM','2016-01-01','C','M','ACT')
    Insert into #Dup values ('1222',2,'SAM','HAM','2016-01-01','C','M','TERM')
    Insert into #Dup values ('1222',3,'SAM','HAM','2016-01-01','C','M','TERM')
    
    Insert into #Dup values ('1222',4,'Genn','Suhan','2018-02-01','C','M','ACT')
    
    Insert into #Dup values ('1224',1,'Lara','Sana','2015-04-01','C','M','ACT')
    Insert into #Dup values ('1224',2,'Lara','Sana','2015-04-01','C','M','TERM')
    
    Insert into #Dup values ('1224',3,'Junaid','G','2014-04-01','C','M','ACT')
    
    
    Insert into #Dup values ('1225',1,'Smith','F','2012-04-01','C','F','TERM')
    Insert into #Dup values ('1225',2,'Smith','F','2012-04-01','C','F','TERM')
    
    Insert into #Dup values ('1225',3,'Roma','Dude','2015-04-01','C','M','ACT')
    
    
    ;with mycte as (
    Select * 
    ,count(*) Over(partition by  Bid,Fname,Lname,REL,Sex) cnt,
    dense_rank() over( partition by  Bid,Fname,Lname,REL,Sex Order by status) 
    + dense_rank() over( partition by  Bid,Fname,Lname,REL,Sex Order by  status desc) - 1 as cntDistinct 
     from   #Dup 
     )
    
     Select Bid,seq_no ,Fname,Lname,dob,REL,Sex, status from mycte 
     WHERE cnt>1 and cntDistinct>1
     order by Bid,seq_no
    
    
    
    drop table #Dup 


    Friday, June 14, 2019 5:51 PM
    Moderator

All replies

  • This is how it can be done...

    SELECT Bid,
    	Fname,
    	Lname,
    	REL,
    	Sex
    FROM #Dup
    GROUP BY Bid,
    	Fname,
    	Lname,
    	REL,
    	Sex
    HAVING count(*) > 1
    	AND count(DISTINCT STATUS) > 1;

    Output:

    Bid  Fname      Lname      REL  Sex
    ---- ---------- ---------- ---- ----
    1222 SAM        HAM        C    M
    1224 Lara       Sana       C    M

    • Marked as answer by SmithDy Friday, June 14, 2019 5:53 PM
    Friday, June 14, 2019 4:26 PM
  • ;WITH CTE_1 AS (
    	SELECT Bid, Fname, Lname, Rel, sex, COUNT(*) AS counts
    	FROM #Dup
    	GROUP BY Bid, Fname, Lname, Rel, sex
    	HAVING COUNT(*) > 1
    ),
    CTE_2 AS (
    	SELECT Bid, Fname, Lname, Rel, sex, status, COUNT(*) AS counts
    	FROM #Dup
    	GROUP BY Bid, Fname, Lname, Rel, sex, status
    	HAVING COUNT(*) > 1
    ),
    CTE_3 AS (
    	SELECT c1.Bid, c1.Fname, c1.Lname, c1.Rel, c1.sex
    	FROM CTE_1 AS c1 
    	LEFT JOIN CTE_2 AS c2 ON c2.Bid = c1.Bid AND c2.Fname = c1.Fname AND c2.Lname = c1.Lname AND c2.Rel = c1.Rel AND c2.sex = c1.sex
    	WHERE c2.counts <> c1.counts OR c2.Bid IS NULL
    )
    
    SELECT d.*
    FROM #Dup AS d
    WHERE EXISTS (SELECT 1 FROM CTE_3 WHERE Bid = d.Bid AND Fname = d.Fname AND Lname = d.Lname AND Rel = d.Rel AND sex = d.sex)


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by SmithDy Friday, June 14, 2019 5:54 PM
    Friday, June 14, 2019 4:30 PM
  • Create table #Dup (Bid char(4), seq_no integer, Fname char(10), Lname char(10), 
    dob datetime, Rel char(1), sex char(1), status char(4))
    Insert into #Dup values ('1222',1,'SAM','HAM','2016-01-01','C','M','ACT')
    Insert into #Dup values ('1222',2,'SAM','HAM','2016-01-01','C','M','TERM')
    Insert into #Dup values ('1222',3,'SAM','HAM','2016-01-01','C','M','TERM')
    
    Insert into #Dup values ('1222',4,'Genn','Suhan','2018-02-01','C','M','ACT')
    
    Insert into #Dup values ('1224',1,'Lara','Sana','2015-04-01','C','M','ACT')
    Insert into #Dup values ('1224',2,'Lara','Sana','2015-04-01','C','M','TERM')
    
    Insert into #Dup values ('1224',3,'Junaid','G','2014-04-01','C','M','ACT')
    
    
    Insert into #Dup values ('1225',1,'Smith','F','2012-04-01','C','F','TERM')
    Insert into #Dup values ('1225',2,'Smith','F','2012-04-01','C','F','TERM')
    
    Insert into #Dup values ('1225',3,'Roma','Dude','2015-04-01','C','M','ACT')
    
    
    ;with mycte as (
    Select * 
    ,count(*) Over(partition by  Bid,Fname,Lname,REL,Sex) cnt,
    dense_rank() over( partition by  Bid,Fname,Lname,REL,Sex Order by status) 
    + dense_rank() over( partition by  Bid,Fname,Lname,REL,Sex Order by  status desc) - 1 as cntDistinct 
     from   #Dup 
     )
    
     Select Bid,seq_no ,Fname,Lname,dob,REL,Sex, status from mycte 
     WHERE cnt>1 and cntDistinct>1
     order by Bid,seq_no
    
    
    
    drop table #Dup 


    Friday, June 14, 2019 5:51 PM
    Moderator
  • Thank you guys
    Friday, June 14, 2019 5:54 PM