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

biotec
biotec used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technology Scientist
Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial