Fees for Period with Amount Paid by Facility and Provider

We would like to calculate the total fees during a period and the amount paid toward those fees, grouped by Facility and by Provider.

The following query uses the “ROLLUP” option to include subtotals for all providers and all facilities, as well as a grand total. In the result set interpret NULL as “ALL”. For example, if you see NULL in the Provider column, read that as “All providers”.

 

SELECT
  COALESCE(g.shorthand,'None') AS "Facility",
  f.provcode AS "Primary Provider",
  SUM(b.amount) AS "Service Amount",
  SUM(coalesce(i.crsplamt,0)) AS "Total Credit"
FROM
  sos.patients a
  JOIN sos.journal b ON a.ptnum=b.ptnum
  JOIN sos.jcharges c ON b.jnum=c.jnum
  JOIN sos.jchgsplits h ON h.jnum=c.jnum
  LEFT OUTER JOIN sos.jcrsplits i ON h.chgsplnum=i.chgsplnum
  LEFT OUTER JOIN sos.jcredits j ON i.jnum=j.jnum
  JOIN sos.ptcsu d ON c.ptcsunum=d.ptcsunum
  LEFT OUTER JOIN sos.providers f ON a.providernum=f.providernum
  LEFT OUTER JOIN sos.facilities g ON d.facilitynum=g.facilitynum
WHERE
  b.trandate BETWEEN '2008-01-01' AND '2015-02-20'
  AND a.licnum=101
  AND (credtype <> 'adjustment' OR credtype is NULL)
GROUP BY
  ROLLUP("Facility", f.provcode)
ORDER BY
  "Facility", f.provcode

Fees and Payments for Period, Grouped by Patient Category

For multiple patient categories, we would like totals billed AND collected for a specified date range.

The following query provides the desired totals, but it is important to note that the payment total is a total of any payments received during the period, not just payments for services rendered during the period. In other words, the payments probably include those for previous services and maybe even some prepayments. Also note that we are using SPLIT amounts for both charges and credits, so if you have corruption that makes the sum of the splits on a transaction different than the transaction amount, the total won’t match reports or queries that use the main amount field.

The following is actually TWO similar queries, one for payments and one for fees, with the use of  UNION to combine the results of each query into a single result set. UNIONed queries must not have their own ORDER statement. Instead, at the end you can specify an ORDER BY followed by the number of the column on which you want to sort, as shown below.

SELECT 
  c.categcode AS Category 
  ,'Payments' AS "Type"
  ,SUM(COALESCE(b.crsplamt,0)) AS "Total"
FROM 
  sos.patients a 
  JOIN sos.rv_creditsplits b ON a.ptnum = b.ptnum 
  JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE 
  a.licnum = 101
  AND b.credtype IN ('CASH','CHECK','CHARGE','OTHER')
  AND b.cre_date BETWEEN '2000-01-01' AND '2014-12-31'
GROUP BY 
  c.categcode

UNION

SELECT 
  c.categcode AS Category 
  ,'Fees' 
  ,SUM(COALESCE(b.chgsplamt,0)) 
FROM 
  sos.patients a 
  JOIN sos.rv_charges b ON a.ptnum = b.ptnum 
  JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE 
  a.licnum = 101
  AND b.trandate BETWEEN '2000-01-01' AND '2014-12-31'
GROUP BY 
  c.categcode
ORDER BY 
  1,2

Average Payment by Service and Payer for Period

I would like to compare payment rates by service codes for our various payers. Specifically, I would like to see the number of services rendered, total payments, and average payments grouped and sorted by service code and payer.

This little query illustrates a bunch of techniques including:

  • IF expression used as a SELECT column
  • Aggregate functions COUNT and SUM
  • CAST a value to a different data type
  • ROUND function
  • GROUP BY ROLLUP( )
SELECT
  (IF pay.payortype = 'I' THEN pay.payorname ELSE 'Private Pay' END IF) AS "Payer",
  chg.srvcode AS "SrvCode",
  COUNT(DISTINCT chg.jnum) AS "SrvCount", 
  SUM(IF COALESCE(cre.credtype,'Adjustment')= 'Adjustment' THEN 0 ELSE cre.crsplamt END IF) AS "TotPaid",
  CAST(ROUND(("TotPaid"/"SrvCount"),2) AS DECIMAL(10,2)) AS "AvgPaid"
FROM
  sos.rv_charges chg
  JOIN sos.ptpayors ptp ON chg.ptpayornum = ptp.ptpayornum
  JOIN sos.payors pay ON ptp.payornum = pay.payornum
  LEFT OUTER JOIN sos.rv_creditsplits cre ON chg.chgsplnum = cre.chgsplnum
WHERE
  chg.trandate BETWEEN '2000-01-01' AND '2000-12-31'
  AND chg.amount > 0
GROUP BY
   ROLLUP("SrvCode","Payer")
ORDER BY
  "SrvCode","Payer"

Referral Source Net Revenue

I need a query which gives me (a) a monthly revenue total and (b) total reversals, such as refunds, for patients with a specific referral source, regardless of provider.

i.e.,  May: $10,000 revenue, $2,000 neg adjustments for all patients with referral source Dr. X (from all datasets).

This is a pretty straightforward little query, but it shows the use of the aggregate function SUM, the date functions YEAR and MONTH, and the use of the LIKE expression to get a match with just part of the referral source’s last name. Note that the WHERE condition is looking only at the referral (last) name field. In this example, the result set would include any referral sources with an “X” in the (last) name field. Also, be sure to adjust the date range to match your need.

SELECT
  a.licnum AS "DataSet", 
  YEAR(c.trandate) AS "YR",
  MONTH(c.trandate) AS "MON",
  SUM((IF c.amount > 0 THEN c.amount ELSE 0 ENDIF)) AS "TotRevenue",
  SUM((IF c.amount < 0 THEN c.amount ELSE 0 ENDIF)) AS "TotReversals",
  "TotRevenue" + "TotReversals" AS "NetRevenue"
FROM 
   sos.patients a
   JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
   JOIN sos.journal c ON a.ptnum = c.ptnum
   JOIN sos.jcredits d ON c.jnum = d.jnum
WHERE 
   d.credtype IN ('cash','check','charge','other')
   AND c.trandate BETWEEN '2012-01-01' AND '2012-12-31'
   AND b.refname LIKE '%X%'
GROUP BY
   DataSet,YR,MON
ORDER BY  
   DataSet,YR,MON

Service Count, Total Charges, and Applied Payments by Provider and Patient Category

We would like to know the income generated for services based on provider and patient category.

The following query shows these results in a “ROLLUP” grid, displaying both detail down to the service code level as well as subtotals and grand totals. When you run the query and examine the results, you should interpret “NULL” in the Provider, PtCategory, and Service columns as meaning “ALL”. For example, the row that shows NULL in all three of these columns is showing the totals for ALL providers, ALL ptcategories, and ALL service codes — in other words, the grand totals. A row that shows a specific provider and a specific category, but NULL in the Service column provides the subtotal of all service codes for the indicated provider and category. You could limit the result set by replacing the wildcard percent sign in the WHERE clause with a specific provider code and/or patient category code if you like.

SELECT
   (a.provfname + ' '+a.provlname) AS "Provider",
   c.categcode AS "PtCategory",
   a.srvcode AS "Service",
   COUNT(DISTINCT jnum) AS "Srv Count",
   SUM(a.chgsplamt) AS "TotalCharges",
   SUM(COALESCE((SELECT SUM(COALESCE(crsplamt,0)) FROM sos.rv_creditsplits
   WHERE  chgsplnum = a.chgsplnum AND credtype IN ('CASH','CHECK','CHARGE')),0)) AS "TotalPayments"
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'
   AND a.provcode LIKE '%'
   AND "PtCategory" LIKE '%'
GROUP BY
  ROLLUP ("Provider","PtCategory","Service")