Category: Queries – Accounting

Referral Source, Patient Count and Charges By Month

Posted on | by SOS Blog | Leave a Comment on Referral Source, Patient Count and Charges By Month

The unique thing about this query is that it creates a grid with the months across the top. For each
Referral Source/Year there is a single row.

SELECT 
   b.refname as "Ref’d By", 
   YEAR(c.trandate) as "Year",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 1 THEN c.ptnum ENDIF)) as "Jan Count",
   SUM(IF MONTH(c.trandate) = 1 THEN c.amount ELSE 0 ENDIF) as "Jan Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 2 THEN c.ptnum ENDIF)) as "Feb Count",
   SUM(IF MONTH(c.trandate) = 2 THEN c.amount ELSE 0 ENDIF) as "Feb Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 3 THEN c.ptnum ENDIF)) as "Mar Count",
   SUM(IF MONTH(c.trandate) = 3 THEN c.amount ELSE 0 ENDIF) as "Mar Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 4 THEN c.ptnum ENDIF)) as "Apr Count",
   SUM(IF MONTH(c.trandate) = 4 THEN c.amount ELSE 0 ENDIF) as "Apr Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 5 THEN c.ptnum ENDIF)) as "May Count",
   SUM(IF MONTH(c.trandate) = 5 THEN c.amount ELSE 0 ENDIF) as "May Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 6 THEN c.ptnum ENDIF)) as "Jun Count",
   SUM(IF MONTH(c.trandate) = 6 THEN c.amount ELSE 0 ENDIF) as "Jun Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 7 THEN c.ptnum ENDIF)) as "Jul Count",
   SUM(IF MONTH(c.trandate) = 7 THEN c.amount ELSE 0 ENDIF) as "Jul Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 8 THEN c.ptnum ENDIF)) as "Aug Count",
   SUM(IF MONTH(c.trandate) = 8 THEN c.amount ELSE 0 ENDIF) as "Aug Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 9 THEN c.ptnum ENDIF)) as "Sep Count",
   SUM(IF MONTH(c.trandate) = 9 THEN c.amount ELSE 0 ENDIF) as "Sep Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 10 THEN c.ptnum ENDIF)) as "Oct Count",
   SUM(IF MONTH(c.trandate) = 10 THEN c.amount ELSE 0 ENDIF) as "Oct Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 11 THEN c.ptnum ENDIF)) as "Nov Count",
   SUM(IF MONTH(c.trandate) = 11 THEN c.amount ELSE 0 ENDIF) as "Nov Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 12 THEN c.ptnum ENDIF)) as "Dec Count",
   SUM(IF MONTH(c.trandate) = 12 THEN c.amount ELSE 0 ENDIF) as "Dec Chgs",
   COUNT(DISTINCT a.ptnum) as "# Count", SUM(c.amount) as "TotChgs"
FROM 
   sos.patients a
   JOIN sos.refsrcs b on a.refsrcnum = b.refsrcnum
   JOIN sos.journal c on a.ptnum = c.ptnum
WHERE 
   c.trantype = 'S' 
   AND c.trandate > '2000-12-31'
GROUP BY 
   b.refname, 
   YEAR(c.trandate)
ORDER BY 
   b.refname, 
   YEAR(c.trandate)

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

Patients with Zero Balance

Posted on | by SOS Blog | Leave a Comment on Patients with Zero Balance 
“Is there a way to print out a list of the active clients who have
zero balances, without printing out a list of ALL of the active clients?
Having this list would make it easy to find the clients who could be moved
to the "inactive" list.”

List of active accounts...
SELECT 
   LastName, 
   FirstName, 
   ID
FROM 
   sos.patients
WHERE 
   flag = 0 
   AND dischargedate IS NULL
ORDER BY 
   LastName, 
   FirstName, 
   ID
List of active accounts with zero balance...
SELECT 
   LastName, 
   FirstName, 
   ID
FROM 
   sos.Patients a JOIN sos.PatientBalance b on a.ptnum = b.ptnum
WHERE 
   b.ptbalance = 0 
   And a.flag = 0 
   AND a.dischargedate IS NULL
ORDER BY 
   LastName, 
   FirstName, 
   ID
List of discharged patients that have no balance...
SELECT 
   LastName, 
   FirstName, ID
FROM 
   sos.Patients a
   JOIN sos.PatientBalance b on a.ptnum = b.ptnum
WHERE 
   b.ptbalance = 0
   AND a.flag = 0
   AND a.dischargedate IS NOT NULL
ORDER BY 
   LastName, FirstName, ID
If you wanted to list by a particular Patient Category, you would
have to use a second table, PtCategs, and you would have to add the
additional condition to the WHERE clause of the query. Here's an example,
assuming the first patient category you want is "ONE":
SELECT 
   a.LastName, 
   a.FirstName, 
   a.ID
FROM 
   sos.patients a
   JOIN sos.ptcategs b ON a.ptcategnum = b.ptcategnum
WHERE 
   a.flag = 0 
   AND a.dischargedate IS NULL 
   AND b.categcode = 'ONE'
ORDER BY 
   LastName, 
   FirstName, 
   ID
The other approach is to add the patient category to the output and sort the
data on that value as well. You would then divide up the report in your word
processor before outputting it:
SELECT 
   b.CategCode, 
   a.LastName, 
   a.FirstName, 
   a.ID
FROM 
   sos.patients a
   JOIN sos.ptcategs b ON a.ptcategnum = b.ptcategnum
WHERE 
   a.flag = 0 
   AND a.dischargedate IS NULL 
   AND b.categcode = 'ONE'
ORDER BY 
   b.CategCode, 
   a.LastName, 
   a.FirstName, 
   a.ID

Payment Latency for Specified Payor Number

Posted on | by SOS Blog | Leave a Comment on Payment Latency for Specified Payor Number 
In order to determine how many days it takes a payor to pay your claims, you
can examine firstbilled and paiddate values in JChgSplits. The following
query will tell you these dates and compute the number of days between them.
Just change the payor number (get the payor number from the second column in
Lookups > Ins Carriers/Plans) and the desired service date range in the
WHERE clause.
SELECT 
   a.jnum,c.amount AS "Fee",
   a.chgsplamt AS "SplitToPayor",
   a.firstbilled,
   a.paiddate,
   datediff(day,a.firstbilled,a.paiddate) AS "PaymentDays"
FROM 
   sos.jchgsplits a
   JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
   JOIN sos.journal c ON a.jnum = c.jnum
WHERE 
   b.payornum = 101
   AND c.trandate BETWEEN '2004-1-1' AND '2004-12-31'

Collections by Service for Period

Posted on | by SOS Blog | Leave a Comment on Collections by Service for Period 
“I have a need for a report that would breakdown monies collected for each service code over a specified time period.”


This one selects based on the service date.


SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND Srv_Date BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode



This one also selects based on the service date, but adds a count of the number of services and the average payment per service.


SELECT 
   srvcode, 
   SUM(crsplamt) AS "Total Payments",
   COUNT(DISTINCT srv_jnum) AS "Count",
   CAST(ROUND("Total Payments"/"Count",2) AS DECIMAL(12,2)) AS "Avg Payment per Svc"
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND Srv_Date BETWEEN '2001-01-01' AND '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode


This one selects based on the date payment was applied.


SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND DateApplied BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode


This one selects based on the date payment was received.


SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
AND 
   Cre_Date BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY
   srvcode
ORDER BY 
   srvcode