Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

unique patients from duplicate patient row entries Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 9:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:35 PM
Points: 15, Visits: 71
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
Post #1360536
Posted Monday, September 17, 2012 9:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 6,146, Visits: 7,198
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1360538
Posted Monday, September 17, 2012 10:22 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:15 AM
Points: 540, Visits: 877
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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1360547
Posted Monday, September 17, 2012 10:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:35 PM
Points: 15, Visits: 71

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')))
Post #1360552
Posted Tuesday, September 18, 2012 1:01 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:15 AM
Points: 540, Visits: 877
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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1360584
Posted Tuesday, September 18, 2012 1:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 6,146, Visits: 7,198
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1360977
Posted Tuesday, September 18, 2012 4:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:35 PM
Points: 15, Visits: 71
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.
Post #1361057
Posted Friday, September 21, 2012 10:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:58 AM
Points: 1,837, Visits: 3,420
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.
Post #1362813
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse