0

I am trying to clean up a SQL table that has duplicate claim_ids. How do I make this query to delete duplicate CLAIM_ID by CollectLogDate

SELECT ID, Claim_ID, InsertDate, CollectLogDate, Count(CollectLogDate) AS CollectLogDate
FROM [dbo].[temp]
GROUP BY ID, Claim_ID, InsertDate, CollectLogDate
Dale K
  • 25,246
  • 15
  • 42
  • 71
Jane Paik Spradlin
  • 1

1 Answers1

0

Below query will find duplicate claim id's between the date range passed and would delete it from the temp table.

delete
from [dbo].[temp]
where claim_id in (
    select claim_id
    from [dbo].[temp]
    where CollectLogDate between 'start_dt' and 'end_dt'
    group by claim_id
    having count(1) > 1
)

If your requirement is different from this then add some sample input and output data to your question.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Divyaansh Bajpai
  • 232
  • 1
  • 8
  • So if there are multiple rows where claim_id = 10 (for example), you propose to delete ALL of those rows and not keep just one? And why are `'start_dt'` and `'end_dt'` part of your solution? – SMor Nov 06 '20 at 19:21
  • Yes above solution does delete all the duplicates ? do you want to keep one and delete rest ? If yes then you can try this. delete T from (select Row_Number() Over(Partition By claim_id order by collectlogdate desc ) As RowNumber, * From [dbo].[temp]) T Where T.RowNumber > 1 – Divyaansh Bajpai Nov 06 '20 at 19:24