Open Dental Forum

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;

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;