Category: Billing Analysis

Outstanding Account Cleanup

Posted on | by SOS Blog | Leave a Comment on Outstanding Account Cleanup

Would it be possible to write a query to do the following:

List the patient name, account number, outstanding balance and provider
For any account that has not had a date of service in 2009
And has had no payments within the past 30 days.

We want to use this to clear out all such outstanding accounts..

The views used in the query below are not super-efficient, so on a large database it will take a good while to run, but it will deliver the results you want.

SELECT
a.lastname + ', '+ a.firstname AS "Name",
a.id AS "Account",
c.provcode AS "Primary-Provider",
(SELECT sos.LASTCHARGEDATE(a.ptnum)) AS "LastService",
(SELECT sos.LASTCREDITDATE(a.ptnum)) AS "LastPayment",
d.ptbalance AS "Balance"
FROM sos.patients a
LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
JOIN sos.patientbalance d ON a.ptnum = d.ptnum
WHERE
"LastService" < '2009-01-01'
AND ("LastPayment" < (TODAY()-30) OR "LastPayment" IS NULL )
ORDER BY
"Name", "Account"
;
OUTPUT TO c:\sos\cleanup.html FORMAT HTML
;

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

Future Dated Transactions And Splits

Posted on | by SOS Blog | Leave a Comment on Future Dated Transactions And Splits

Having trouble reconciling your aging reports? The likely reason is that you have charge splits or credit splits with dates in the future. Aging is based on SPLIT dates, not service dates or credit dates. On the charge side, SOS’s aging reports use the charge split date; on the credit side they use the Date Applied in the credit split.

Use the following queries to find splits and transactions with future dates:

/* --------- charges --------------*/

SELECT
  licnum AS "Dataset", (lastname + ', '+firstname+' / '+ id) AS "Patient",
  trandate AS "Service Date", provcode AS "Provider", srvcode AS "Service",
  amount AS "Fee", chgsplamt AS "Split Amt", chgspldate AS "Split Date",
  jnum AS "Journal#"
FROM
  sos.rv_charges
WHERE
  chgspldate > today() OR trandate > today()


/*--------- credits ------------*/

SELECT
  licnum AS "Dataset", fullnameid AS "Patient",
  cre_date AS "Credit Date", credtype AS "Credit Type",
  payorname AS "Payor", cre_amount AS "Total Credit",
  crsplamt AS "Split Amt", dateapplied AS "Date Applied",
  cre_jnum AS "Journal#"
FROM
  sos.rv_creditsplits
WHERE
  dateapplied > today() OR cre_date > today()

	


	





	

			

	

		
Fees By Year and Week
		

			Posted on  |  by SOS Blog |  Leave a Comment on Fees By Year and Week		

			

	

		
We want to see a breakdown of fees charged by year and week.


Here we use some interesting functions, such as DATEPART, which returns a specified portion of a date value, as well as more commonly used aggregate functions like SUM, MIN and MAX. We also use the CAST function to return the result of the AVG function as a number with two decimal places. We also add a little spice by throwing in the ROLLUP function to give us totals and subtotals. Where you see NULL in the results, interpret it as meaning “ALL”. Therefore a NULL in the YEAR column should be interpretted as the total line for all the years.


SELECT
  DATEPART(yy,trandate) AS "YEAR",
  DATEPART(wk,trandate) AS "WEEK_Number",
  MIN(trandate) AS "FROM",
  MAX(trandate) AS "THROUGH",
  CAST (AVG(amount) AS DECIMAL(12,2)) AS "AVERAGE FEE",
  SUM(amount) AS "TOTAL FEES CHARGED"
FROM
  sos.journal
WHERE
  trandate BETWEEN '2000-01-01' AND '2003-12-31'
  AND licnum = 101
  AND trantype = 'S'
GROUP BY
  ROLLUP("YEAR","WEEK_Number" )
ORDER BY
  "YEAR","WEEK_Number"

	


	





	

			

	

		
Fees and Service Count by Payor, CPT, Service, SortCode, Month, and Year
		

			Posted on  |  by SOS Blog |  Leave a Comment on Fees and Service Count by Payor, CPT, Service, SortCode, Month, and Year		

			

	

		
I have a query to tell me the sum of fees and number of services by payor, cpt code, service code, and SortCode, but I would like to break down that data by month and year as well. Can that be done?



This query illustrates the use of a subquery as a virtual table. That is, we use an embedded SELECT statement in the main query’s FROM clause to create a result set that we then treat as if it were a table, JOINing it to other tables to give us the results that we want. To get the month and year grouping,

add YEAR(trandate), MONTH(trandate) to the SELECT list, the GROUP BY, and the ORDER BY.


Note that the column order in the SELECT clause makes no difference, so you can shuffle them around as you like, but be sure to separate columns with commas, except no comma after the last one.


Assuming you would still want the sortcode and payor to be higher order grouping, the query would become:


SELECT
  YEAR(a.trandate) AS "Year",
  MONTH(a.trandate) AS "Month",
  COALESCE(d.payorname,'ALL PAYORS') AS payorname,
  COALESCE(b.cptcode,'ALL CPTS') AS cptcode,
  COALESCE(a.srvcode,'ALL SRVCODES') AS srvcode,
  a.sortcode AS sortcode,
  COUNT(*) AS "N",
  SUM(a.amount) AS "SumFees"
FROM
  (SELECT DISTINCT jnum,srvcode,servicenum,ptpayornum,trandate,amount,sortcode FROM sos.rv_charges) a
  JOIN sos.services b ON a.servicenum = b.servicenum
  JOIN sos.ptpayors c ON a.ptpayornum = c.ptpayornum
  JOIN sos.payors d ON c.payornum = d.payornum
WHERE
  d.payortype = 'I'
  AND trandate BETWEEN '2008-4-01' AND '2008-9-11'
GROUP BY
  ROLLUP(a.sortcode,d.payorname,"Year","Month", b.cptcode, a.srvcode)
ORDER BY
  sortcode,payorname,"Year","Month",cptcode,srvcode