troubleshooting Question

SQL Query Returning Multiple Lines--and Join doesn't appear to be correct

Avatar of CMCITD
CMCITDFlag for United States of America asked on
Microsoft SQL ServerSQL
7 Comments1 Solution103 ViewsLast Modified:
I have the following SQL Code

select distinct 'ORG', h.Name, CAST(h.Birthdate as DATE), h.SocialSecurityNumber, p.Provider_UnvUserID, p.Provider_UnvUserID, adx.AbstractDiagnosisCode_MisDxID AS ICD9, 
adx.AbstractDiagnosisCode_MisDxID AS ICD10, mdm.Name AS DXNAME, 'HSP' AS VISITTYPE, 'HSP' AS DEPARTMENTNAME, 'HSP' AS LOCNAME,
r.AccountNumber AS ACCOUNTID, ads.DischargeDateTime AS DISCHARGEDATE
 from [CYD-DR01].[livefdb].[dbo].[RegAcct_Main] r
JOIN [CYD-DR01].[livefdb].[dbo].[RegAcct_Providers] p ON p.VisitID = r.VisitID
JOIN [CYD-DR01].[livefdb].[dbo].[RegAcct_ProviderData] pd ON pd.VisitID = r.VisitID
JOIN [CYD-DR01].[livefdb].[dbo].[HimRec_Main] h ON h.PatientID = r.PatientID
JOIN [CYD-DR01].[livefdb].[dbo].[AbsAcct_Diagnoses] ad ON ad.VisitID = r.VisitID
JOIN [CYD-DR01].[livefdb].[dbo].[RegAcct_AbsDxs] adx ON adx.VisitID = r.VisitID
JOIN [CYD-DR01].[livefdb].[dbo].[MisDx_Main] mdm ON mdm.MisDxID = adx.AbstractDiagnosisCode_MisDxID
JOIN [CYD-DR01].[livendb].[dbo].[AdmDischargeInfo] ads ON ads.VisitID = r.VisitID
where r.Location_MisLocID = 'MEDSURG' and p.ISPrimaryCareProvider = 'Y' and pd.PrimaryCareProvider_UnvUserID IN ('DOC1', 'DOC2', 'DOC3', 'DOC4') 
and ad.DiagnosisUrnID = 1 
order by h.Name desc

I am getting the data I expect--however the last part of my where clause "And ad.DiagnosisUrnID = 1" doesn't appear to be working--and I am getting several entries for each patient, each with a unique diagnosis.  If I add the DiagnosisUrnID to the select statement, every line shows a 1--however if I go into that table "AbsAcct_Diagnosis", I can see the proper numbers behind the lines (1, 2,3).  Essentially, I'm trying to pull only the primary diagnosis for those patients.

I'm joining on Visit ID--which appears to be working--so I'm unsure why it's returning a ad.DiagnosisUrnID of 1 on each line when that doesn't match that table.  Thanks for any help!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros