A/R (filters) query

[drh]

New to Open, is there a filter/query in A/R reports that will allow us to determine the time period when last payment activity occurred as we are trying to clean up old accounts that have not been tidied up in the past.

ex: accounts from the year 2017 then move on to 2018 etc

Thanks in advance
DrH

[Tom Zaccaria]

Does this help your situation?
drtmz

/*590 List of Patients with balance over 90 days AND Billing Type is "Bad Debt-Sent to collections". Includes Patients Name, Patient Address, Phone Numbers, SSN, DOB, Patnum, Last Date of Service, Date of Last Payment, Last Amount Paid, Balance Due Amount*/
SELECT p.LName, p.FName, p.PatNum AS 'PatNo.',p.HmPhone,p.WkPhone,p.WirelessPhone AS Wireless, p.SSN, p.BirthDate,
(SELECT MAX(pl.ProcDate)
FROM procedurelog pl
INNER JOIN patient p2 ON p2.PatNum=pl.PatNum
WHERE pl.ProcStatus=2 AND p2.Guarantor=p.PatNum) AS 'DateLastSeen',
(SELECT MAX(ps.DatePay)
FROM paysplit ps
INNER JOIN patient p2 ON p2.PatNum=ps.PatNum
WHERE p2.Guarantor=p.PatNum) AS 'DateLastPaid',
(SELECT SUM(ps.SplitAmt) FROM paysplit ps
INNER JOIN patient p3 ON p3.PatNum=ps.PatNum
WHERE p3.Guarantor=p.PatNum AND ps.DatePay=
(SELECT MAX(ps.DatePay)
FROM paysplit ps
INNER JOIN patient p2 ON p2.PatNum=ps.PatNum
WHERE p2.Guarantor=p.PatNum)) AS $AmtLastPaid,
p.Address, p.Address2, p.City,p.State, p.ZIP,p.BalTotal
FROM patient p
INNER JOIN definition d ON p.BillingType=d.DefNum
WHERE ItemName LIKE('%Sent to Collections%') AND
p.BalTotal>.005 AND p.BalOver90>.005

[Tom Zaccaria]

This one might be better for your situation.
drtmz

/*6*/ SELECT CONCAT(LName,', ',FName,' ',MiddleI)
,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 LName,FName

[drh]

Tom Zaccaria, Thanks!
The second one is in the ballpark
The thing that I would like is to be able to specify and sort out a date range. Say a specific year or a quarter. Our old software has limited capacity to customize a report. We switched to open and can see the potential to tailor reports to just what we need if adept at SQL language. Have decided to learn it a bit but until then rely on helpful folks such as you.
Thanks again,
Darrell

[Tom Zaccaria]

Part of my problem is I do not have your situation to check the query. Our accounts are up to date. You always want to test run a query before using it live. I can play around with the paysplit table a little and see what might work.
Or a quick shout out to support may help.

drtmz