unique patients from duplicate patient row entries

Question

unique patients from duplicate patient row entries

boehnc

Ten Centuries

Points: 1001
More actions
September 17, 2012 at 9:15 pm

#264297

I have one table "patients"...one row per patient.
Patient.ID, Patient.Name, Patient.address, Patient.gender
1/Jim/123 elm/M
I have another table of patient diagnosis codes. It can have multiple dx codes per patient.
dx.patientid/dx.dxcode/dx.dxcodedesc
1/23/diabetes
1/35/chf
2/33/pnem
2/34/stroke
2/56/headache
I need to bring back in a table unique patients where dxcode = 23 or 34.
one patient can have 23 and 34..I just need to bring back whether they had one or the other. If I do a left outer join, a patient with 23 and 34 will bring back two entries. How do I have it bring back just one unique entry? Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

boehnc (9/17/2012)
I need to bring back in a table unique patients where dxcode = 23 or 34.
one patient can have 23 and 34..I just need to bring back whether they had one or the other. If I do a left outer join, a patient with 23 and 34 will bring back two entries. How do I have it bring back just one unique entry? Thanks

If you setup schema and data, we'll be able to provide you actual code, but in the meantime, psuedocode will have to do:

SELECT

mt.ID, MAX( c.dxCode)

FROM

MainTable AS mt

JOIN

dxcodes AS c

ON mt.Id = c.Id

GROUP BY

mt.ID

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

boehnc (9/17/2012)
I have one table "patients"...one row per patient.
Patient.ID, Patient.Name, Patient.address, Patient.gender
1/Jim/123 elm/M
I have another table of patient diagnosis codes. It can have multiple dx codes per patient.
dx.patientid/dx.dxcode/dx.dxcodedesc
1/23/diabetes
1/35/chf
2/33/pnem
2/34/stroke
2/56/headache
I need to bring back in a table unique patients where dxcode = 23 or 34.
one patient can have 23 and 34..I just need to bring back whether they had one or the other. If I do a left outer join, a patient with 23 and 34 will bring back two entries. How do I have it bring back just one unique entry? Thanks

Left outer join !!

Can you please post your query with expected output.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

boehnc Ten Centuries Points: 1001 More actions · Answer 3

this is basically the code...this join... left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId...will give me multiple rows per patient

i might need help with the diagnosis order/code statements in the where code as well?

select a.EncounterKey,a.DivisionName, a.VisitId,a.MedicalRecordNumber,a.InOutIndicator,a.EDFlag,a.AgeYear,a.PatientGender, a.ChargeTotal,pa.City,pa.State,a.PatientZip

from UserView.vwAccount a

left join UserView.vwPayer p on p.PayerPlanKey = a.PayerPrimaryKey

left join UserView.vwPatient pa on pa.PatientKey=a.PatientKey

left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId

where a.DischargeDate>= '2011-01-01' and a.DischargeDate<='2012-01-01'

and p.PayerClassGroupName in ('blue cross','true blue')

and a.ChargeTotal>0

and ((d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between '140.0' and '239.99')

or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between 'v10.00' and 'v10.99')

or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between 'v76.0' and 'v76.99')

or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode in ('v66.1','v66.2','v67.1','v67.2','v58.42')))

rhythmk SSCertifiable Points: 7271 More actions · Answer 4

boehnc (9/17/2012)
this is basically the code...this join... left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId...will give me multiple rows per patient
i might need help with the diagnosis order/code statements in the where code as well?
select a.EncounterKey,a.DivisionName, a.VisitId,a.MedicalRecordNumber,a.InOutIndicator,a.EDFlag,a.AgeYear,a.PatientGender, a.ChargeTotal,pa.City,pa.State,a.PatientZip
from UserView.vwAccount a
left join UserView.vwPayer p on p.PayerPlanKey = a.PayerPrimaryKey
left join UserView.vwPatient pa on pa.PatientKey=a.PatientKey
left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId
where a.DischargeDate>= '2011-01-01' and a.DischargeDate<='2012-01-01'
and p.PayerClassGroupName in ('blue cross','true blue')
and a.ChargeTotal>0
and ((d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between '140.0' and '239.99')
or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between 'v10.00' and 'v10.99')
or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode between 'v76.0' and 'v76.99')
or (d.DiagnosisOrder between '1' and '4' and d.DiagnosisCode in ('v66.1','v66.2','v67.1','v67.2','v58.42')))

Did you try the pseudo code posted by Kraig ? or try with DISTINCT.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

boehnc (9/17/2012)
this is basically the code...this join... left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId...will give me multiple rows per patient
i might need help with the diagnosis order/code statements in the where code as well?

If you're not going to be able to extrapolate from psuedocode, we're going to need a lot more details from you to provide you a working example.

Please see the first link in my signature for what we'll require to properly setup working code for your example and to show you exactly what you'll need to do. This includes schema for test structures, sample data, and expected results from that data, in a format we can cut and paste into our own servers and after review run scripts against what they create.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

boehnc Ten Centuries Points: 1001 More actions · Answer 6

thank you for your responses and link. I'll remember that for future questions I might have. They pseudo code worked fine..thanks for the advice.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 7

You could also solve this using EXISTS.

select p.*

from partients p

where exists(select * from dx where dx.patiendid=p.partientid and dx.dxcode in (23,24))

I'm not sure if this approach will perform better than Evil Craig F's solution so you need to test it. At least the there is no aggregation steps in my approach.