Category: Billing Analysis

Detailed List of Charge Balances by Payor On Specified Date

Posted on | by SOS Blog | Leave a Comment on Detailed List of Charge Balances by Payor On Specified Date

We have an audit coming up and would like to have a detailed listing, down to the charge split level, of unpaid charges on the last day of our fiscal year. It would be most helpful to have the list sorted by payor and date.

In the following query, you must replace the date in two places with the last date of your fiscal period. Remember that this is a listing of charge splits, so there can be several lines for the same service item, even for a single payor. The sort order keeps splits for the same payor and service together.

SELECT
  //patient
  a.licnum AS "DataSet",
  a.id AS "AccountID",
  a.lastname AS "LastName",
  a.firstname AS "FirstName",
  //service
  a.jnum AS "TransactionNum",
  a.trandate AS "SrvDate",
  a.provcode AS "ProvCode",
  a.srvcode AS "SrvCode",
  a.amount AS "TotalFee",
  a.balance AS "TotalBalance",
  //payor
  TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
  a.chgsplamt AS "AmtChargedPayor",
  (SELECT COALESCE(SUM(crsplamt),0)
       FROM sos.jcrsplits
       WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30') AS "PayorCreditsApplied",
  (a.chgsplamt - "PayorCreditsApplied") AS "PayorBalance",
  //billing dates
  COALESCE(STRING(a.firstbilled),'') AS "DateFirstBilled",
  COALESCE(STRING(a.lastbilled),'') AS "DateMostRecentlyBilled"
FROM
  sos.rv_charges a
  JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
  JOIN sos.payors c ON b.payornum = c.payornum
WHERE
   "AmtChargedPayor" <> 0
  AND "PayorBalance" <> 0
  AND  (a.paiddate IS NULL OR a.paiddate > '2008-09-30')
ORDER BY
  "Payor","SrvDate","TransactionNum"
The following version combines splits if a single payor has more than one split on the same service charge entry.
SELECT
  //patient
  a.licnum AS "DataSet",
  a.id AS "AccountID",
  a.lastname AS "LastName",
  a.firstname AS "FirstName",
  //service
  a.jnum AS "TransactionNum",
  a.trandate AS "SrvDate",
  a.provcode AS "ProvCode",
  a.srvcode AS "SrvCode",
  a.amount AS "TotalFee",
  a.balance AS "TotalBalance",
  //payor
  TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
  SUM(a.chgsplamt) AS "AmtChargedPayor",
  SUM((SELECT COALESCE(SUM(crsplamt),0)
          FROM sos.jcrsplits
          WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30')) AS "PayorCreditsApplied",
  ("AmtChargedPayor" - "PayorCreditsApplied") AS "PayorBalance",
  //billing dates
  MIN(COALESCE(STRING(a.firstbilled),'')) AS "DateFirstBilled",
  MAX(COALESCE(STRING(a.lastbilled),'')) AS "DateMostRecentlyBilled"
FROM
  sos.rv_charges a
  JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
  JOIN sos.payors c ON b.payornum = c.payornum
WHERE
  (a.paiddate IS NULL OR a.paiddate > '2008-09-30')
GROUP BY
  "DataSet","Payor","Lastname","Firstname","ID","SrvDate","ProvCode",
  "SrvCode","TotalFee","TotalBalance","TransactionNum"
HAVING
  "AmtChargedPayor" <> 0
  AND "PayorBalance" <> 0
ORDER BY
  "DataSet","Payor","SrvDate","TransactionNum"

	


	





	

			

	

		
Count Of Patients By Insurance Plan
		

			Posted on  |  by SOS Blog |  Leave a Comment on Count Of Patients By Insurance Plan		

			

	

		
The SOS report “Patients by Insurance Carrier” lists every active patient. Can that report be trimmed to just giving the carrier and the number of active patients within each carrier, perhaps also with a grand total of active patients? Knowing the exact amount of patients we serve is helpful when negotiating with carriers about rates, etc.



The following query gives a count by Plan and (primary) Provider. For the count across providers, remove line 3, line 9, and the comma and d.provcode from the GROUP BY and ORDER BY clauses. Better yet, look at the second version of the query below, featuring a ROLLUP that gives you all the totals and subtotals without the need to edit.


SELECT
  c.payorname AS "Ins Plan",
  d.provcode,
  count(a.ptnum) AS "N"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum
  JOIN sos.providers d ON a.providernum = d.providernum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
  AND c.payortype = 'I'
GROUP BY
  c.payorname, d.provcode
ORDER BY
  c.payorname, d.provcode


The version below adds the ROLLUP operator to the GROUP BY. That creates a result set that includes NULL in various cells. Read NULL as “ALL.” Therefore if you see NULL in the ProvCode column, it means that this count is for all providers. There is also a row with NULL in both the Plan and Provider columns, meaning all plans and all providers, which in this case is the number of patients in the result set.


SELECT
  c.payorname AS "Ins Plan",
  d.provcode,
  count(a.ptnum) AS "N"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum
  JOIN sos.providers d ON a.providernum = d.providernum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
  AND c.payortype = 'I'
GROUP BY
  ROLLUP(c.payorname, d.provcode)
ORDER BY
  c.payorname, d.provcode

	


	





	

			

	

		
CPT Code Count for Period and Payor
		

			Posted on  |  by SOS Blog |  Leave a Comment on CPT Code Count for Period and Payor		

			

	

		
Does anybody have a query that will allow me to get a count of CPT code units by insurance plan (BCBS) over a specified time frame?


I specifically need to know which and how many CPT code units were billed to BCBS over the last 6 months.




This query produces a count of services for each CPT code for a specified period. The results are limited to service entries where there is at least one split to the designated payor. In this example, we are selecting any payor that has the word “BLUE” somewhere in the payor name: f.payorname LIKE ‘%BLUE%’ but you could use a specific name: f.payorname = ‘BCBS’ or payor number: f.payornum = 1234 or a group of payor numbers: f.payornum IN (1234, 5678, 123456) by changing the appropriate line in the WHERE clause below.


SELECT
  d.cptcode AS "CPT",
  COUNT(DISTINCT a.jnum) AS "SvcCount"
FROM
  sos.journal a
  JOIN sos.jcharges b ON a.jnum = b.jnum
  JOIN sos.jchgsplits c ON b.jnum = c.jnum
  JOIN sos.services d ON b.servicenum = d.servicenum
  JOIN sos.ptpayors e ON c.ptpayornum = e.ptpayornum
  JOIN sos.payors f ON e.payornum = f.payornum
WHERE
  trandate BETWEEN '2002-01-01' AND '2002-03-31'
  AND f.payorname LIKE '%BLUE%'
GROUP BY
  d.cptcode
ORDER BY
  d.cptcode