First Attempt at SQL

[Rickliftig]

Having the ability to pull out data from Open Dental is like having the keys to the kingdom! This script is modeled on one I have been using in my old management system. I merelymodded one of the SQL examples in the OD Help files.

This script sorts your receivables by balance owed, and aging. In other words, the oldest, highest balances are listed in order (along with their phone numbers and last payment date) so that your front desk can easily concentrate on the biggest fish (stinkers) first.

This is a great report if you merely want to lie awake and stew about why these people committed to treatment and payment and never came through. And since we all have ironclad financial policies in place, I assume this report will rarely be used <G>.

I haven't figured out the magic command, but there must be an easy way to reverse the order so that the biggest balances are on top. And the sorting is not quite perfect, but it does a good job of pointing out the worst offenders.

You can easily modify this report by having the sort done by BalTotal first to get a full picture of who owes you the most.

Cut Here---------------------------------

SELECT CONCAT(LName,', ',FName,' ',MiddleI)
,HmPhone,WkPhone,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90
,BalTotal,InsEst,BalTotal-InsEst AS $pat,
DATE_FORMAT(MAX(paysplit.ProcDate),'%m/%d/%Y') AS lastPayment
FROM patient
LEFT JOIN paysplit
ON paysplit.PatNum=patient.PatNum
WHERE (patstatus != 2)
AND (Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005' OR BalTotal < '-.005')
GROUP BY patient.PatNum
ORDER BY BalOver90,Bal_61_90,Bal_31_60, Bal_0_30,LName,FName


Cut here -----------------------------------------

best,

Rick

[nathansparks]

Yeah, you can switch the order and add secondary sorting by using
ORDER BY FieldName1 DESC, FieldName2 DESC, FieldName3 ASC

ASC is ascending, DESC is descending

Nathan