We have been asked to give statistics regarding how many dual diagnosis clients we treat during a specific time period. I can run a report requesting the primary diagnosis; however, at times I need to run a report on one specific diagnosis that may be listed as #2, 3 or even 4.
SELECT
Providers. ProvCode, Providers.ProvLName, Providers.ProvFName,
Patients.LastName, Patients.FirstName, Patients.IntakeDate,
PtVars.LFeeDate AS "Last Service",
PtCSUDx.DxCode1, PtCSUDx.DxCode2, PTCSUDx.DxCode3, PtCSUDx.DxCode4
FROM
sos.Patients
LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum
LEFT OUTER JOIN sos.PtCSU ON Patients.PtNum = PtCSU.PtNum
LEFT OUTER JOIN sos.PtCSUDx ON PtCSU.PtCSUNum = PtCSUDx.PtCSUNum
LEFT OUTER JOIN sos.PtVars ON Patients.PtNum = PtVars.PtNum
WHERE
Patients.LicNum = 101
AND PtCSU.TypeFlag = 'D'
AND (SELECT COUNT(*)
FROM sos.journal
/* service date range goes on next line */
WHERE trantype = 'S' AND amount > 0 AND trandate BETWEEN '2000-01-01' AND '2008-06-30') > 0
/*<-- desired Dx code goes on next line*/
AND 'F10.150' IN (PtCSUDx.DxCode1, PtCSUDx.DxCode2, PTCSUDx.DxCode3, PtCSUDx.DxCode4)
ORDER BY
Providers.ProvCode, Patients.LastName, Patients.FirstName