Category: Payment Analysis

Patients with Unapplied Payments and Outstanding Balances

Posted on | by SOS Blog | Leave a Comment on Patients with Unapplied Payments and Outstanding Balances

I need a report that shows me all of the patients who have an unapplied credit AND a patient balance. At the end of the month I need to run a this report and go back and apply credits to balances that somehow I have missed throughout the month. I have tried to do this by running the unapplied credit report but that report includes patients without a balance and is very time consuming to go into each of those accounts. For example, a patient received a statement for $75.00 yet she had $70 in unapplied credits.

While all the information you need is in the standard Aging by Patient report, this query filters that information so the results contain only those accounts and payors of interest.

SELECT
   (PtPayors.Age0to30 + PtPayors.Age31to60 + PtPayors.Age61to90 + PtPayors.Age91to120 + PtPayors.AgeOvr120) AS "BALANCE",
   PtPayors.Ageunapplied AS "UNAPPLIED",
   Patients.ID,
   Patients.LastName + ', ' + Patients.FirstName AS "Patient Name",
   Payors.PayorName + ' ' + Payors.FirstName AS "Payor Name",
   PtPayors.PayorNum
 FROM  
   SOS.Patients Patients
   LEFT OUTER JOIN SOS.PtPayors PtPayors ON Patients.PtNum= PtPayors.PtNum
   LEFT OUTER JOIN SOS.Payors Payors ON PtPayors.PayorNum=Payors.PayorNum
 WHERE
   "UNAPPLIED" > 0
   AND "BALANCE" > 0
 ORDER BY
  "Patient Name", Patients.ID, PtPayors.PayorNum

Patients with Appointments Today and a Significant Balance, Sorted by Site and Provider

Posted on | by SOS Blog | Leave a Comment on Patients with Appointments Today and a Significant Balance, Sorted by Site and Provider

List the names of patients with balances of $120 or more, sorted by site and provider, who are scheduled to be seen on the day the query is run. 

SELECT
  a.sitename+' ('+a.sitecode+')' AS "site",
  (a.provlname+', '+a.provfname+' ('+provcode+')') AS "provider",
  a.ptfullname,
  a.id,
  b.ptbalance
FROM
  sos.rv_appts a
  JOIN sos.pt_noninsbalance b ON a.ptnum = b.ptnum
WHERE
  b.ptbalance >= 120
  AND a.cancelflag = 0
  AND a.apptdate = TODAY()
ORDER BY
  "site","provider",a.ptfullname,a.id

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
;

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()

	


	





	

			

	

		
Credits Without Sort Codes By Primary Provider
		

			Posted on  |  by SOS Blog |  Leave a Comment on Credits Without Sort Codes By Primary Provider		

			

	

		
We have discovered that some credit entries were entered without specifying a Sort Code, which we need for reporting purposes. I need a total amount of such credits, by provider.


SELECT
 r.provcode,
 r.provlname,
 SUM(amount) AS "TotalCredits"
FROM
 sos.journal j,
 sos.patients p,
 sos.providers r
WHERE
 j.ptnum=p.ptnum AND
 p.providernum=r.providernum AND
 j.trandate BETWEEN '2000-03-01' AND '2016-03-15' AND
 j.sortcode IS NULL
GROUP BY
 r.provcode, r.provlname