Treatment Plan Reviews In Next Thirty Days
Back to Query Directory
Case Manager displays "Next Treatment Plan Review Date" – I would like to print a list of upcoming treatment plan reviews.
There are several advanced structures in this query, including two levels of subqueries, the inner-most one being a UNION of two queries. All of this is required because of the fact that the next review date could be in the tpheaders table (if there have not yet been any reviews for the patient) or the tpreviews table (if one or more reviews have already been recorded).
Subqueries can be treated exactly as if they were actual tables by wrapping them in parentheses and assigning an alias. If you look at the ninth line you will see that instead of a table name after the JOIN, there is a subquery that finally ends on line 13 with the closing parenthesis and the alias name "d". If you look deeper at just that subquery (the one starting on line 9 and ending on line 13) you will see that it selects from yet another subquery that runs from line 10 through line 12. This one is actual two small queries, with the result set of each combined using UNION into a single result set, assigned the arbitrary alias "x". Queries used in place of actual tables must always be assigned an alias, even if the alias is never referenced.
The nature of this query allows us to code a dynamic range for the 30 days starting today ...
BETWEEN TODAY() AND (TODAY()+30)
... instead of having to enter a date range every time we run the query. If you want a longer window, just change "30" to the desired number of days.
SELECT
d.nextreviewdate AS "Next Review",
(a.lastname+', '+a.firstname+' / '+a.id) AS "Patient",
c.provcode AS "Primary Provider"
FROM
patients a
JOIN v_tpheaders b ON a.ptnum = b.ptnum
LEFT OUTER JOIN providers c ON a.providernum = c.providernum
JOIN (SELECT tpheadernum,max(nrd) AS "nextreviewdate"
FROM (SELECT tpheadernum,COALESCE(nextreviewdate,'1990-01-01') AS nrd FROM tpheaders
UNION
SELECT tpheadernum,COALESCE(nextreviewdate,'1990-01-01') FROM v_tpreviews WHERE rowstatus = 'O' ) x
GROUP BY tpheadernum ) d ON b.tpheadernum = d.tpheadernum
WHERE
a.flag = 0
AND a.dischargedate IS NULL
AND d.nextreviewdate BETWEEN TODAY() AND (TODAY()+30)
ORDER BY "Next Review","Patient"
d.nextreviewdate AS "Next Review",
(a.lastname+', '+a.firstname+' / '+a.id) AS "Patient",
c.provcode AS "Primary Provider"
FROM
patients a
JOIN v_tpheaders b ON a.ptnum = b.ptnum
LEFT OUTER JOIN providers c ON a.providernum = c.providernum
JOIN (SELECT tpheadernum,max(nrd) AS "nextreviewdate"
FROM (SELECT tpheadernum,COALESCE(nextreviewdate,'1990-01-01') AS nrd FROM tpheaders
UNION
SELECT tpheadernum,COALESCE(nextreviewdate,'1990-01-01') FROM v_tpreviews WHERE rowstatus = 'O' ) x
GROUP BY tpheadernum ) d ON b.tpheadernum = d.tpheadernum
WHERE
a.flag = 0
AND a.dischargedate IS NULL
AND d.nextreviewdate BETWEEN TODAY() AND (TODAY()+30)
ORDER BY "Next Review","Patient"
CategoryQueries
