Category: Queries – Payors

Average Payment by Service and Payer for Period

Posted on | by SOS Blog | 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"

Mailing Labels for Patients with Specific Insurance Payors

Posted on | by SOS Blog | Leave a Comment on Mailing Labels for Patients with Specific Insurance Payors 
We need to send letters to all of our psychiatrists' patients catagorized by insurance payor. How can I
get mailing labels for these letters? The scenario is that our docs have removed themselves from the
Magellan and United Behavioral Health panels and will only accept fee-for-services from those patients.
Obviouly we need to inform the patients - several times and in several ways. The first step is to identify
them and prepare labels for the letters to be sent.

OK, so we can identify the patients using the primary provider code. Good. Let's start with this query. I'll
include the payorname in the output so you can check the export. You can ignore that column when you
read the data into Word to create your labels.
SELECT
   (a.firstname + ' '+a.lastname) as "name",
   a.addr1 as "addr1",
   a.addr2 as "addr2",
   (a.city+', '+a.state+' '+a.zip) as "addr3",
   b.payorname as "payor"
FROM
   sos.rv_patients a 
   join sos.rv_policies b on a.ptnum = b.ptnum
WHERE
   a.flag = 0 
   and a.priprvcode='prv' 
   and (b.payorname like '%magellan%' or b.payorname like '%ubh%') 
   and (b.active is null or b.active <= current date) 
   and (b.inactive is null or b.inactive >= current date)
; output to c:\sos\labels.csv format ascii
The resulting csv file can be loaded into Excel for additional manipulation, if you like, or can be used as
is. If you check the help in Word regarding mailing labels, that should take you the rest of the way. Oh,
note the fourth line up from the bottom. This one is where I am specifying the payor names. If you have
a UBH payor that you have spelled out as "United Behavioral Health" you should add another "or...." to
that condition, or change the '%ubh%' condition. This expression means that it will include payors with
the characters "ubh" anywhere in the name. That could result in some payors you do not want, such as a
payor named "Flubhead" or something like that. You should be able to fine tune that with a little
experimentation. On the fifth line up from the bottom you must replace 'prv' with your psychiatrist's
provider code. The other conditions restrict the output to active patients with active matching policies as
of today's date.

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'

Credit Breakdown by Payor for Service Date Range

Posted on | by SOS Blog | Leave a Comment on Credit Breakdown by Payor for Service Date Range 
Breaks down credits applied to services in a date range groups by insurance payor.


Select
   payorname,
   payornum,
   credtype,
   count(distinct cre_jnum) as "Number",
   sum(crsplamt) as "Paid"
From
   sos.rv_creditsplits
Where
   srv_date between '2001-01-01' and '2001-01-31'
   and payortype = 'I'
   and systranflag not in ('TT','TF')
group by
   credtype,
   payornum,
   payorname

	


	





	

			

	

		
Credit Breakdown by Payor Type for Service Date Range
		

			Posted on  |  by SOS Blog |  Leave a Comment on Credit Breakdown by Payor Type for Service Date Range		

			

	

		
Breaks down credits applied to services in a date range by type and groups by payor type.




 


Select
   payortype,
   Credtype,
   count(distinct cre_jnum) as "Number",
   sum(crsplamt) as "Paid"
From
   sos.rv_creditsplits
Where
   srv_date between '2001-01-01' and '2001-01-31'
   and systranflag not in ('TT','TF')
group by
   credtype,
   payortype