Category: Queries – Payors

Services For Specified Payors For Period

Posted on | by SOS Blog | Leave a Comment on Services For Specified Payors For Period

I need a query that would yield all services provided for August and
September grouped by the effected insurance carriers. It would be
helpful if the services listed within each carrier group were in
ascending order by service code. 

SELECT DISTINCT
  c.payorname, a.srvcode, a.lastname, a.firstname, a.id,
  a.trandate,d.insdid,d.insgroup,
  a.provcode, a.amount
FROM
  sos.rv_charges a
  JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
  JOIN sos.ptpolicies d ON b.ptpayornum = d.ptpayornum
  JOIN sos.payors c ON b.payornum = c.payornum
WHERE
  trandate BETWEEN '2008-08-01' AND '2008-09-30'
  /* replace with appropriate payor numbers in line below */
  AND c.payornum IN (122,25626,9750,178,8807,120,8439,25627,25584,25585)
ORDER BY
  c.payorname, a.srvcode, a.lastname, a.firstname, a.id, a.trandate

Non-Insurance Balance By Place of Service, Date Range, and Provider

Posted on | by SOS Blog | Leave a Comment on Non-Insurance Balance By Place of Service, Date Range, and Provider

I am looking for a query that prints out total balance remaining on patients only (not insurance) by LOC code (e.g.,11 or 61) by date range by provider.

The following query gives the balance itemized by patient. To get just summary totals, remove “a.lastname,a.firstname,a.id” from the SELECT and GROUP BY clauses.

SELECT
a.provcode,a.lastname,a.firstname,a.id,SUM(a.chgsplbal) AS "Balance"
FROM
sos.rv_charges a
JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN sos.payors c ON b.payornum = c.payornum
JOIN sos.poscodes d ON a.poscodenum = d.poscodenum
JOIN sos.patients e ON a.ptnum = e.ptnum
WHERE
a.licnum = 101 AND   /*look only at main data set*/
e.flag = 0 AND          /* just active list patients*/
c.payortype <> 'I'     /* ignore insurance splits*/
AND d.defcode IN ('11','61')    /* place of service code is 11 or 61*/
AND a.trandate BETWEEN '2009-01-01' AND '2009-03-31'    /* date range*/
GROUP BY
a.provcode,a.lastname,a.firstname,a.id

Mailing Labels For Payors In Collections

Posted on | by SOS Blog | Leave a Comment on Mailing Labels For Payors In Collections

I need mailing labels for Payors with a collection date of 1/1/11 and greater, excluding accounts that are paid in full.

The date that a payor is sent to collections is a field in the payors table. The balance owed by the payor is a sum of the field “postedbal” in a related table, ptpayors. The primary key that uniquely identifies a payor is the payornum value. JOIN the two tables together using this shared value, payornum, which is found in both tables.

There may be more than one ptpayors row for each payor, so to eliminate duplicate rows in the output (which will subsequently be loaded into Excel and used as data for a MailMerge label in Word), we add the DISTINCT modifier after SELECT. That will have the effect of removing any duplicate rows.

The COALESCE function in the collection date condition is there to assign a value to payors with NULL collection dates. We use an arbitrary date that should always be outside of the desired selection range to prevent those rows from being included in the results or preventing any results from being delivered because of the ambiguous nature of NULL values.

SELECT DISTINCT
   TRIM(a.firstname) AS "First Name",
   TRIM(a.payorname) AS "Last Name",
   a.addr1 AS "Address 1",
   a.addr2 AS "Address 2",
   TRIM(city) AS "City",
   TRIM(state) AS "State",
   zip
 FROM  
   sos.payors a
   JOIN sos.ptpayors b ON a.payornum = b.payornum
 WHERE  
   COALESCE(CollectDate,'1990-01-01') >= '2011-01-01'
   AND b.postedbal > 0
 ORDER BY
   "Last Name", "First Name"
 ;
 OUTPUT TO c:\sos\labels.html FORMAT HTML

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"

	


	





	

			

	

		
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