SQL - Finding duplicate rows of data

Transact-SQL
1

Hi All,

I know this is probably something really simple, but I thought I would ask. I have to create a one time query to find rows that our duplicate, however I don't have a primary key. This is what I have below, Please let me know if there is a simpler way. Once I determine that the TIN, DOB, charge is the same I need to find out what the other Claim ID is, which is [Claim ID TCN]

Example would be I have a claim 12548523. I can't match on the claim because the numbers are different. But I can match on TIN, DOB and Total Charges. Then I can get the other Claim ID that I need which would be the Claim ID - TCN. I hope this makes sense if not, I can try to explain more.

Select Distinct
CLM_ID1,
CLM_6A as [Date of Service - CBR],
CLM_5,
CLM_1A,
CLM_1A1,
CLM_1A2,
CLM_1A3,
---CLM_1B, patient address
CLM_12A as [Patient Last name - CBR],
CLM_12B as [Patient First Name - CBR],
CLM_14 as [Date of Birth - CBR],
CLM_tchg as [Total Charge - CBR],
[Claim ID TCN],
[Total Charge - TCN]

FROM IMPACT.dbo.CLM
Inner JOIN (Select Distinct clm_id1 as [Claim ID TCN], clm_5 as [TIN - TCN],clm_tchg as [Total Charge - TCN], clm_12a as [Patient Last name],CLM_12B as [Patient First Name],CLM_14 as [Date of Birth], CLM_6A as [Date of Service - TCN] FROM IMPACT.dbo.CLM where CLM_CC1 = '13320' AND CLM_6A >= '01/01/2019') tcn on [TIN - TCN] = clm_5 and [Date of Service - TCN] = CLM_6A and [Total Charge - TCN] = clm_tchg and [Date of Birth] = clm_14
where CLM_CC1 = '13620' AND

CLM_6A >= '01/01/2019' and
/**These are CBR claims that have a TIN that is PAR/
clm_id1 IN ('06781581',
'06781587',
'06783343',
'06767479',
'06767490',
'06767498',
'06767502',
'06767504',
'06778706',
'06778710',
'06778711',
'06778712',
'06787711',
'06787712',
'06787721',
'06763089',
'06764739',
'06764745',
'06764750',
'06764751',
'06764752',
'06764753',
'06764754',
'06764755',
'06764758',
'06764759',
'06764760',
'06764761',
'06764762',
'06764763',
'06764764',
'06764765',
'06764767',
'06764768',
'06764769',
'06764770',
'06783342',
'06784588',
'06764738',
'06765600',
'06765601',
'06765602',
'06765603',
'06765604',
'06765605',
'06765606',
'06765607',
'06765608',
'06765609',
'06765610',
'06765611',
'06765612',
'06765613',
'06765614',
'06765615',
'06765616',
'06765617',
'06765618',
'06765619',
'06765620',
'06765621',
'06765622',
'06765623',
'06765624',
'06765625',
'06765626',
'06765627',
'06765628',
'06765629',
'06765630',
'06765631',
'06765634',
'06765635',
'06765636',
'06765642',
'06765643',
'06765644',
'06765645',
'06765646',
'06765648',
'06765649',
'06765650',
'06765651',
'06765652',
'06765653',
'06765654',
'06765655',
'06765656',
'06765658',
'06765659',
'06765660',
'06765661',
'06765662',
'06765663',
'06765664',
'06765665',
'06765666',
'06765667',
'06765668',
'06765669',
'06765670',
'06765671',
'06765672',
'06765673',
'06765677',
'06765680',
'06765681',
'06765682',
'06765683',
'06765684',
'06765685',
'06765686',
'06765687',
'06765688',
'06765689',
'06765690',
'06765691',
'06765692',
'06765693',
'06765694',
'06765695',
'06765696',
'06765697',
'06765698',
'06765699',
'06765700',
'06765701',
'06765702',
'06765703',
'06765704',
'06765705',
'06765706',
'06765707',
'06765708',
'06765709',
'06765710',
'06765711',
'06765712',
'06765713',
'06765714',
'06765715',
'06765716',
'06765717',
'06765718',
'06765719',
'06765720',
'06765721',
'06765722',
'06765723',
'06765724',
'06765725',
'06765726',
'06765727',
'06765728',
'06765729',
'06765730',
'06765731',
'06765732',
'06765733',
'06765734',
'06765735',
'06765736',
'06765737',
'06765738',
'06765739',
'06765740',
'06765741',
'06765742',
'06765743',
'06765744',
'06765745',
'06765746',
'06765747',
'06765748',
'06765750',
'06765751',
'06765752',
'06765753',
'06765754',
'06765755',
'06765756',
'06765757',
'06765758',
'06765759',
'06765760',
'06765761',
'06765762',
'06765763',
'06765764',
'06765765',
'06765766',
'06765767',
'06765768',
'06765769',
'06765770',
'06765771',
'06766648',
'06766649',
'06766650',
'06766651',
'06766652',
'06766653',
'06766654',
'06766655',
'06766656',
'06766657',
'06766658',
'06766659',
'06766660',
'06766661',
'06766662',
'06766663',
'06766664',
'06766665',
'06766666',
'06766667',
'06766668',
'06766669',
'06766672',
'06766674',
'06766675',
'06766676',
'06766677',
'06766678',
'06766679',
'06766680',
'06766681',
'06766682',
'06766683',
'06766684',
'06766685',
'06766686',
'06766687',
'06766688',
'06766689',
'06766690',
'06766691',
'06766692',
'06766693',
'06766694',
'06766695',
'06766697',
'06766698',
'06766699',
'06766700',
'06766701',
'06766702',
'06766703',
'06766704',
'06766705',
'06766706',
'06766707',
'06766708',
'06766709',
'06766710',
'06766711',
'06766712',
'06766713',
'06766714',
'06766715',
'06766716',
'06766717',
'06766718',
'06766719',
'06766720',
'06766721',
'06766722',
'06766723',
'06766724',
'06766725',
'06766726',
'06766727',
'06766728',
'06766729',
'06767480',
'06767481',
'06767482',
'06767483',
'06767484',
'06767485',
'06767486',
'06767487',
'06767488',
'06767489',
'06767491',
'06767492',
'06767493',
'06767494',
'06767495',
'06767496',
'06767497',
'06767499',
'06767500',
'06767501',
'06767503',
'06767505',
'06767506',
'06767507',
'06767508',
'06767509',
'06767510',
'06767511',
'06767512',
'06767513',
'06767515',
'06767516',
'06767517',
'06767518',
'06767519',
'06767520',
'06767521',
'06767522',
'06767523',
'06767524',
'06767525',
'06767526',
'06767527',
'06767528',
'06767529',
'06767530',
'06767531',
'06767532',
'06767533',
'06767534',
'06767535',
'06767536',
'06767537',
'06767538',
'06767539',
'06767540',
'06767541',
'06767542',
'06767543',
'06767544',
'06767545',
'06767546',
'06767549',
'06767550',
'06767551',
'06767552',
'06767553',
'06767554',
'06767555',
'06767556',
'06767557',
'06767558',
'06767559',
'06767560',
'06767561',
'06767562',
'06767563',
'06767564',
'06767565',
'06767566',
'06767567',
'06767568',
'06767569',
'06767570',
'06767571',
'06767572',
'06767573',
'06767574',
'06767575',
'06767576',
'06767577',
'06767578',
'06767579',
'06767580',
'06767581',
'06767582',
'06767583',
'06767584',
'06767585',
'06767586',
'06767587',
'06767588',
'06767589',
'06767590',
'06767591',
'06767592',
'06767593',
'06767594',
'06767595',
'06767596',
'06767597',
'06767598',
'06767599',
'06767600',
'06767601',
'06767602',
'06767603',
'06767604',
'06767605',
'06767606',
'06767607',
'06767608')

2

This is one way of doing it:

select top(1) with ties
       * /* the fields you want to show */
  from impact.dbo.clm
 where clm_cc1='13620'
   and clm_6a>=cast('20190101' as datetime)
/*****
 * These are CRB claims that have a TIN that is PAR
 */
   and clm_id1 in ('06781581'
                  ,'06781587'
                   .
                   .
                   .
                  ,'06767608'
                  )
/*
 *****/
 order by sign(sum(-1) over(partition by clm_5
                                        ,clm_14
                                        ,clm_tchg
                           )
              +1
              )
;