Category: Billing Analysis

Number of Services for Patients by Service Code and Rendering Provider

Posted on | by SOS Blog | Leave a Comment on Number of Services for Patients by Service Code and Rendering Provider

Here is the information I need:
Clients name: (using the Intake as of Sept. 2003)
Providers name:
Number of times in Individual sessions (CPT 90806 Code: IP)
Number of times in Group (CPT 90853 Code: Grp)
Is that possible to get the clients name and the number of sessions they have been in for individual and
group sessions?

I took a little liberty with your request to show the number of sessions of all rendered services, but this
can be trimmed down, if you like. You did not indicate whether the provider was the rendering or primary.
I used rendering, so you will see the number of sessions for each provider for each patient (if there are
multiple providers rendering services to a single patient). The output of the query will go to a
Lotus-format file called sessioncount.wks in the C:\SOS folder. Excel will open this file without complaint.

If you want to change the file name or location, modify the last line of the query. (Note that you can
un-comment line 15 if you want to restrict the results to just the IP and GRP service codes. I would
suggest running the more inclusive query first in case the providers are using different codes than you
expect.)

Note that we use JOIN rather than LEFT OUTER JOIN to link the tables in this case. No data will be
omitted because all these values and rows must be present for every charge entry. You can’t have a
charge without a patient (ptnum), provider (providernum), or service (servicenum).

SELECT
   (UPPER(a.LastName)+', '+a.FirstName+' / '+a.id) as "Client",
   c.ProvCode as "Rendering_Provider",
   d.CPTCode,
   d.srvcode,
   COUNT(*) as "NumSessions"
FROM
   sos.patients a
   JOIN sos.jcharges b on a.ptnum = b.ptnum
   JOIN sos.providers c on b.providernum=c.providernum
   JOIN sos.services d on b.servicenum=d.servicenum
WHERE
   a.intakedate > '2003-08-31'
   AND a.flag = 0
   // AND d.srvcode IN ('IP','GRP')
   // remove the beginning slashes on line above to return only those service codes
GROUP BY
   client,
   rendering_provider,
   cptcode,
   srvcode
ORDER BY
   client,
   rendering_provider,
   cptcode,
   srvcode
;OUTPUT TO c:\sos\sessioncount.html FORMAT HTML

Service Count By Provider, Patient Category and Service Code For Period

Posted on | by SOS Blog | Leave a Comment on Service Count By Provider, Patient Category and Service Code For Period

I need total # of services rendered broken down by provider, patient category, and service code, for a specific time frame.

SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
c.categcode AS "Category",
a.srvcode AS "Service",
COUNT(DISTINCT jnum) AS "Srv Count"
FROM
sos.rv_charges a
JOIN sos.patients b ON a.ptnum = b.ptnum
LEFT OUTER JOIN sos.ptcategs c ON b.ptcategnum = c.ptcategnum
WHERE
a.trandate BETWEEN '1980-01-01' AND '2008-12-31'
GROUP BY
"Provider","Category","Service"

Projected Collections for Period by Site

Posted on | by SOS Blog | Leave a Comment on Projected Collections for Period by Site

We go through our daily schedules each day to look at the amount of money to be collected from the patients. The amount is the amount of outstanding non-insurance balance plus current copayment. Would it be possible to write a query that does this?

The query would calculate amounts for each office site using the red “Sites” in the optional tab of the Scheduler. It would provide the total copayment due, non-insurance balance due, and the sum of these two values.

A Grand Total of all sites would be great as well.

This is the same query executed with grouping for site specific totals, and without grouping for the grand totals. The two queries have the same exact structure, so we can use UNION to combine the two query results in a single result set.

SELECT
COALESCE(SiteCode,'None Specified') AS "SITE",
SUM(COALESCE((SELECT ptbalance FROM sos.pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM sos.rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
  AND a.cancelflag = 0
GROUP BY "SITE"

UNION

SELECT
'ALL SITES',
SUM(COALESCE((SELECT ptbalance FROM sos.pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM sos.rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
  AND a.cancelflag = 0