Annual Sessions Remaining in Authorization
Back to Query Directory
Is there any way with SOS to track the total sessions a patient has used towards their yearly total? We know that sessions used towards authorized sessions are tracked but would like to be able to track total sessions for our therapists so that yearly maximums are not gone over.
(Query Contributed by Vince Bellwoar)
We explored this a few years ago and were able to get two birds with one stone.
Under the ADDITIONAL TAB in the schedule reminder box, we use the first two spaces for the annual benefit limit for the year (e.g. 20 if the plan will pay for 20 outpatient sessions).
Seth wrote us a nifty Query that:
(1.) counts the number of sessions used in a certain time period (you can designate the current year or put in the an actual date range).
(2.) Subtracts this amount from the number we inputted under the schedule reminder box in ADDITIONAL screen.
If have included the query below. The results tell us of anyone with 4 or less visits remaining for the year--plenty of time to make a clinical plan.
I said "two birds". The other benefit is that by listing the annual benefit limit is the additional tab, our secretaries can quickly find the amount. (We also put copay amount in this area).
Note that once parity goes into full effect by the end of 2009, most insurers will not have an annual benefit limit--unless they try to put an annual benefit limit on how many times a the subscriber can see their primary care doc.
[Additional Comments: There are some commented out options in this SQL code (lines starting with "") that would give you the option of limiting the query to certain listed payor numbers, or all payor numbers other than those listed. As written, neither of these options is enabled, so all payors are included. Vince also has a specified list of cpt codes (not service shorthand codes) to be included in the count. Be sure to tune that list to your requirements. You also can modify the warning threshold from his setting of 4)
SELECT
a.lastname AS "PtLast name",
a.firstname AS "First name",
a."id" AS "Acct ID",
a.dob,
h.dxcode,
(IF ISNUMERIC(e.Fld1) = 1
THEN CAST(e.Fld1 AS INTEGER)
ELSE IF ISNUMERIC(LEFT(a.NoSchedReason,2)) = 1
THEN CAST(LEFT(a.NoSchedReason,2) AS DECIMAL)
ELSE 0
ENDIF
ENDIF
) AS "Annual Benefit Max",
(SELECT COUNT(*)
FROM journal jou JOIN jcharges chg ON jou.jnum = chg.jnum JOIN services srv ON chg.servicenum = srv.servicenum
WHERE jou.ptnum = a.ptnum
AND srv.cptcode IN
('90801','90805','90806','90846','90847','90853','90862') /* CPT LIST HERE */
AND YEAR(jou.trandate) = YEAR(CURRENT DATE) /* this line sets date range */
) AS "# Visits Used",
("Annual Benefit Max" - "# Visits Used") AS "# remaining",
COALESCE(f.provcode,'None') AS "Primary Prov", c.payorname AS "Carrier", d.insdid AS "Insured ID"
FROM
patients a
JOIN ptpayors b ON a.ptnum = b.ptnum
JOIN payors c ON b.payornum = c.payornum JOIN ptpolicies d ON b.ptpayornum = d.ptpayornum
LEFT OUTER JOIN uddatapol e ON d.uddatanum = e.uddatanum
LEFT OUTER JOIN providers f ON a.providernum = f.providernum LEFT OUTER JOIN ptcsu g ON a.ptcsunum = g.ptcsunum
LEFT OUTER JOIN dx h ON g.dx1 = h.dxnum WHERE a.flag = 0
//AND c.payornum IN (101,102,103) /* PAYOR NUMBER LIST HERE TO LIMIT PAYORS TO THOSE LISTED OR USE NEXT*/
//AND c.payornum NOT IN (101,102,103) /*PAYOR NUMBER LIST HERE TO EXCLUDE SPECIFIED PAYORS */
AND a.dischargedate IS NULL
AND "Annual Benefit Max" > 0
AND "# remaining" <= 4 /* SPECIFY VISIT THRESHOLD, EG: 4 VISITS OR LESS REMAINING */
AND b.ptpayornum IN (SELECT ptpayornum
FROM rv_charges
WHERE YEAR(trandate) = YEAR(CURRENT DATE)
AND ptnum = a.ptnum)
a.lastname AS "PtLast name",
a.firstname AS "First name",
a."id" AS "Acct ID",
a.dob,
h.dxcode,
(IF ISNUMERIC(e.Fld1) = 1
THEN CAST(e.Fld1 AS INTEGER)
ELSE IF ISNUMERIC(LEFT(a.NoSchedReason,2)) = 1
THEN CAST(LEFT(a.NoSchedReason,2) AS DECIMAL)
ELSE 0
ENDIF
ENDIF
) AS "Annual Benefit Max",
(SELECT COUNT(*)
FROM journal jou JOIN jcharges chg ON jou.jnum = chg.jnum JOIN services srv ON chg.servicenum = srv.servicenum
WHERE jou.ptnum = a.ptnum
AND srv.cptcode IN
('90801','90805','90806','90846','90847','90853','90862') /* CPT LIST HERE */
AND YEAR(jou.trandate) = YEAR(CURRENT DATE) /* this line sets date range */
) AS "# Visits Used",
("Annual Benefit Max" - "# Visits Used") AS "# remaining",
COALESCE(f.provcode,'None') AS "Primary Prov", c.payorname AS "Carrier", d.insdid AS "Insured ID"
FROM
patients a
JOIN ptpayors b ON a.ptnum = b.ptnum
JOIN payors c ON b.payornum = c.payornum JOIN ptpolicies d ON b.ptpayornum = d.ptpayornum
LEFT OUTER JOIN uddatapol e ON d.uddatanum = e.uddatanum
LEFT OUTER JOIN providers f ON a.providernum = f.providernum LEFT OUTER JOIN ptcsu g ON a.ptcsunum = g.ptcsunum
LEFT OUTER JOIN dx h ON g.dx1 = h.dxnum WHERE a.flag = 0
//AND c.payornum IN (101,102,103) /* PAYOR NUMBER LIST HERE TO LIMIT PAYORS TO THOSE LISTED OR USE NEXT*/
//AND c.payornum NOT IN (101,102,103) /*PAYOR NUMBER LIST HERE TO EXCLUDE SPECIFIED PAYORS */
AND a.dischargedate IS NULL
AND "Annual Benefit Max" > 0
AND "# remaining" <= 4 /* SPECIFY VISIT THRESHOLD, EG: 4 VISITS OR LESS REMAINING */
AND b.ptpayornum IN (SELECT ptpayornum
FROM rv_charges
WHERE YEAR(trandate) = YEAR(CURRENT DATE)
AND ptnum = a.ptnum)
CategoryQueries
