Query question, balance?

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
noestervemb
Posts: 59
Joined: Sat Jul 09, 2016 7:45 am

Query question, balance?

Post by noestervemb » Thu Nov 10, 2016 10:35 am

I found the query below. It lets me set the interval of when a patient was last seen. I am trying to add the patient balance for each patients as a column. That way i can get a list and it shows a $0 balance i an inactivate without checking their account but if it has a balance i would need to check their account . When we converted we got over 4000 patients that needs to be inactivated so it already a huge task but this would make it easier. Anyone know of a query like that?


SET @pos=0, @FromDate='1990-01-01' , @ToDate='2013-01-01';
SELECT pa.ChartNumber, pa.LName, pa.FName, MAX(ProcDate) AS 'Last Seen'
FROM patient pa, procedurelog pl
WHERE pl.PatNum = pa.PatNum
AND pa.patstatus = '0'
AND pl.procStatus IN(2,3)
GROUP BY pl.PatNum
HAVING (MAX(ProcDate) BETWEEN @FromDate
AND @ToDate)
ORDER BY pa.Lname;

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Query question, balance?

Post by cmcgehee » Thu Nov 10, 2016 11:27 am

The patient's balance is stored in the BalTotal column on the patient's guarantor. You would just need to join on the patient table again to get the guarantor and display the BalTotal column.

SET @pos=0, @FromDate='1990-01-01' , @ToDate='2013-01-01';
SELECT pa.ChartNumber, pa.LName, pa.FName, MAX(ProcDate) AS 'Last Seen',g.BalTotal
FROM patient pa, procedurelog pl, patient g
WHERE pl.PatNum = pa.PatNum
AND pa.patstatus = '0'
AND pl.procStatus IN(2,3)
AND g.PatNum=pa.Guarantor
GROUP BY pl.PatNum
HAVING (MAX(ProcDate) BETWEEN @FromDate
AND @ToDate)
ORDER BY pa.Lname;
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply