none
Please kindly assist me in the below query .? RRS feed

  • Question

  • Hi All,

    Please kindly assist me to get duplicate Policy Holder ID numbers  present across Partner ?

    Policy Table has - Policy_Number,Policy_Holder_ID_Number,Policy_Holder_Name,Partner_id

    Partner Table has - Partner_id,Partner_name.

    Say eg,

    Policy_Holder_id_Number :- '1234' is present in Partner :- 'ABC' and 'BCD'

    Please note, a Policy_Holder_ID_Number is present in Policy Table multiple times as because of different import months.

    Thanks,


    • Edited by kalsubu Wednesday, September 18, 2019 11:01 AM
    Wednesday, September 18, 2019 10:53 AM

All replies

  • Can you post sample data along with desired result? To see the duplicates 

    A this time I'd like to show some techniques to deal with duplicates
    CREATE TABLE TableWithDups
    (
    row_id int identity(1,1),
    col1 int,
    col2 int
    )
    INSERT INTO TableWithDups (col1 ,col2 ) VALUES (1,10)
    INSERT INTO TableWithDups (col1 ,col2 ) VALUES (1,10)
    INSERT INTO TableWithDups (col1 ,col2 ) VALUES (4,2)
    INSERT INTO TableWithDups (col1 ,col2 ) VALUES (3,3)
    INSERT INTO TableWithDups (col1 ,col2 ) VALUES (3,3)
    INSERT INTO TableWithDups (col1 ,col2 ) VALUES (9,20)

    PRINT 'Duplicates in Table'
    SELECT * FROM TableWithDups
    WHERE EXISTS
    (SELECT * FROM TableWithDups TWD
    WHERE TWD.row_id <> TableWithDups.row_id
    AND TWD.col1 = TableWithDups.col1
    AND TWD.col2 = TableWithDups.col2)

    PRINT 'Duplicates to be Deleted'
    SELECT * FROM TableWithDups
    WHERE EXISTS
    (SELECT * FROM TableWithDups TWD
    WHERE TWD.row_id < TableWithDups.row_id 
    AND TWD.col1 = TableWithDups.col1
    AND TWD.col2 = TableWithDups.col2 )


    PRINT 'Executig Deletion of Duplicates '
    DELETE FROM TableWithDups
    WHERE EXISTS 
    (SELECT * FROM TableWithDups TWD
    WHERE TWD.row_id < TableWithDups.row_id 
    AND TWD.col1 = TableWithDups.col1
    AND TWD.col2 = TableWithDups.col2 )

    SELECT * FROM TableWithDups

    If you are using SQL Server 2005 take a look at the below script
    WITH Duplicates
    AS
    ( SELECT *, ROW_NUMBER()OVER(PARTITION BY col1 , col2 ORDER BY col1 , col2 ) AS cnt FROM TableWithDups)
    DELETE FROM Duplicates WHERE cnt> 1;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 18, 2019 11:24 AM
    Moderator
  • Hi All,

    Please kindly assist me to get duplicate Policy Holder ID numbers  present across Partner ?

    Policy Table has - Policy_Number,Policy_Holder_ID_Number,Policy_Holder_Name,Partner_id

    Partner Table has - Partner_id,Partner_name.

    Say eg,

    Policy_Holder_id_Number :- '1234' is present in Partner :- 'ABC' and 'BCD'

    Please note, a Policy_Holder_ID_Number is present in Policy Table multiple times as because of different import months.

    Thanks,


    you can get like this

    SELECT Policy_Holder_ID_Number
    FROM Policy
    GROUP BY Policy_Holder_ID_Number
    HAVING COUNT(DISTINCT Partner_Id) > 1

    to remove duplicates use a logic like

    SELECT *
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY Policy_Holder_ID_Number ORDER BY Policy_Number) AS Seq,
    *
    FROM Policy
    )t
    WHERE Seq = 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, September 18, 2019 12:01 PM
    Moderator
  • Hi, 

    I did have multiple Policy_Holder_Id_Number in Policy Table due to import_month.

    For eg, From Jan to Aug 2019, I got 8 records.

    But my requirement is Policy_Holder_id_Number present in different Partner_id,

    Say eg,

    Policy_Holder_id_Number :- '1234' is present in Partner :- 'ABC' and 'BCD'

    Wednesday, September 18, 2019 12:34 PM
  • Hi kalsubu,

    Can you please post your original table and desired table? So that we are able to help in the correct direction. 

    Waiting for your table. 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 19, 2019 9:00 AM
  • Hi, 

    I did have multiple Policy_Holder_Id_Number in Policy Table due to import_month.

    For eg, From Jan to Aug 2019, I got 8 records.

    But my requirement is Policy_Holder_id_Number present in different Partner_id,

    Say eg,

    Policy_Holder_id_Number :- '1234' is present in Partner :- 'ABC' and 'BCD'

    that is exactly what the first query does for you

    It identifies the cases where same Policy_Holder_id_Number is existing for multiple Partner_ids

    Did you try it at all?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, September 19, 2019 9:24 AM
    Moderator