======Patients with Diagnosis in Any Position, By Provider======

Back to [[QueryDirectory Query Directory]]

//
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.
//

%%(sql)
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
Patients
LEFT OUTER JOIN Providers ON Patients.ProviderNum = Providers.ProviderNum
LEFT OUTER JOIN PtCSU ON Patients.PtNum = PtCSU.PtNum
LEFT OUTER JOIN PtCSUDx ON PtCSU.PtCSUNum = PtCSUDx.PtCSUNum
LEFT OUTER JOIN PtVars ON Patients.PtNum = PtVars.PtNum
WHERE
Patients.LicNum = 101
AND PtCSU.TypeFlag = 'D'
AND (SELECT COUNT(*)
FROM 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 '300.14' IN (PtCSUDx.DxCode1, PtCSUDx.DxCode2, PTCSUDx.DxCode3, PtCSUDx.DxCode4)
ORDER BY
Providers.ProvCode, Patients.LastName
%%


----
CategoryQueries