Patients with Diagnosis in Any Position, By Provider
Back to 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.
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
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
