Need to get one person_id per cpt4_code_id but getting many dups.

Need to get one person_id per cpt4_code_id but getting many dups. Need to do a RowNo probably but not sure what columns to use.

select  p.person_id,  p.date_of_birth, pe.enc_timestamp,a.appt_date,pp2.cpt4_code_id, pm.description as ProviderName, p.last_name, p.first_name
,pe.cob1_insured_person_id, pe.cob1_payer_id, pa.payer_name  
       from patient_encounter pe
       inner join person p with(nolock) on pe.person_id = p.person_id 
       INNER JOIN patient_procedure AS pp2     ON p.person_id = pp2.person_id
       INNER JOIN appointments AS a   with(nolock)  ON pe.enc_id = a.enc_id
       inner join provider_mstr pm on pm.provider_id = a.rendering_provider_id 
        left join payer_mstr pa on pe.cob1_payer_id = pa.payer_id
          where a.appt_kept_ind = 'Y' and a.resched_ind = 'N' and a.delete_ind  = 'N'        
and (p.date_of_birth > '20110401' and p.date_of_birth < '20180331')
  and (pe.enc_timestamp > '20180101' and pe.enc_timestamp < '20180331')
           AND pp2.cpt4_code_id  Like 'D1%'
       and a.location_id = 'EFAF5A81-18CF-426C-A985-863C7B5D16D0'

Open in new window

biotecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NerdsOfTechTechnology ScientistCommented:
The duplicate rows are from the JOINS (different data exists in one or more columns, or matches). Therefore, I suggest you use GROUP BY, DISTINCT, or LIMIT to constrain the results per cpt4_code_id.

For a quick test see what this gives you:

SELECT DISTINCT s1.cpt4_code_id, s1.* FROM (
select  p.person_id,  p.date_of_birth, pe.enc_timestamp,a.appt_date,pp2.cpt4_code_id, pm.description as ProviderName, p.last_name, p.first_name
,pe.cob1_insured_person_id, pe.cob1_payer_id, pa.payer_name  
       from patient_encounter pe
       inner join person p with(nolock) on pe.person_id = p.person_id 
       INNER JOIN patient_procedure AS pp2     ON p.person_id = pp2.person_id
       INNER JOIN appointments AS a   with(nolock)  ON pe.enc_id = a.enc_id
       inner join provider_mstr pm on pm.provider_id = a.rendering_provider_id 
        left join payer_mstr pa on pe.cob1_payer_id = pa.payer_id
          where a.appt_kept_ind = 'Y' and a.resched_ind = 'N' and a.delete_ind  = 'N'        
and (p.date_of_birth > '20110401' and p.date_of_birth < '20180331')
  and (pe.enc_timestamp > '20180101' and pe.enc_timestamp < '20180331')
           AND pp2.cpt4_code_id  Like 'D1%'
       and a.location_id = 'EFAF5A81-18CF-426C-A985-863C7B5D16D0') s1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
biotecAuthor Commented:
I think this seems to get each cpt4 code per visit per person. Unless further testing proves different, I really appreciate the help. Thanks
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.