Category: Queries – Accounting

Fees for Period with Amount Paid by Facility and Provider

Posted on | by sos documentation | Leave a Comment on 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

Posted on | by sos documentation | Leave a Comment on 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

Posted on | by sos documentation | Leave a Comment on 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

Posted on | by sos documentation | Leave a Comment on 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

Account Aging Balance Detail on Specified Date

Posted on | by sos documentation | Leave a Comment on Account Aging Balance Detail on Specified Date

I want to see a detailed view of the balance for a particular account on a previous date. The aging report gives me the balance on that date, but I want to see the detail for each item that contributes to the aggregate balance figure.

I think this may give you what you need. You will notice on line 15 (just before the WHERE):

 –,*

 If you remove the dashes at the beginning of that line, it will add many more columns in the output. You can pick and choose from those to add above that line, then put the dashes back in to hide the extra columns. In the WHERE, just change the ID to the account you want to check out.

This query will list only those chargesplits that were still carrying a balance on June 30, 2012. For any other date, change the dates in both line 13, 14, and line 29.

select 
  a.jnum,
  a.id,
  a.trandate as "service date",
  a.srvcode,
  a.amount as "fee",
  d.payorname as "responsible payor",
  a.chgspldate as "chg split date",
  a.chgsplamt as "chg split amt",
  (select 
      coalesce(sum(crsplamt),0) from sos.rv_creditsplits 
   where 
      cre_date < '2012-07-01'  
      and dateapplied < '2012-07-01' 
      and chgsplnum = a.chgsplnum) as "credits applied",
  a.chgsplamt - "credits applied"  as "chg split balance",
  b.crsplamt as "credit split amt",
  b.credtype as "credit type", 
  b.dateapplied as "date applied"
 --,*
from 
  sos.rv_charges a 
  left outer join sos.rv_creditsplits b on a.chgsplnum = b.chgsplnum
  join sos.ptpayors c on a.ptpayornum = c.ptpayornum
  join sos.payors d on c.payornum = d.payornum
where 
  a.id = '123456789'
  and "chg split balance" <> 0
  and a.chgspldate < '2012-07-01'
order by 
  "service date","chg split date"