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

SELECT
mt.ID, MAX( c.dxCode)
FROM
 MainTable AS mt
JOIN
dxcodes AS c
ON mt.Id = c.Id
GROUP BY
mt.ID

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

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')))

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?