Revision [151]
Last edited on 2008-11-10 16:30:38 by SethKriegerAdditions:
======Patients with Diagnosis in Any Position, By Provider======
Deletions:
Revision [123]
Edited on 2008-10-30 14:14:11 by adminAdditions:
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
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
Deletions:
paste your tested query here
