Sub Select Query to get Last Payment and Date
Posted on 2007-12-03
I have a query that gets a list of clients who have a small balance. I also need to include the last payment amount and last payment date in the query. The following query is what I have so far, but it takes way to long to execute.
select distinct(pd.clientid), pd.clientfirstname, pd.clientlastname, c.creditorid, c.creditorname, dw.id, dw.accountnumber, dw.balanceCurrent, dw.balanceInitial,
(SELECT TOP 1 (r.paymentAmount) from receipts, personalData where receipts.clientid = personalData.clientid ORDER BY receiptid desc) as LastReceipt,
(SELECT TOP 1 (r.datePaymentMade) from receipts, personalData where receipts.clientid = personalData.clientid ORDER BY receiptid desc) as LastPaymentDate
from personalData pd, debtWorksheet dw, creditors c, dmpStatus dmp, disbursements d, receipts r
where pd.clientid = dw.clientid and dw.creditorid = c.creditorid and pd.clientid = dmp.clientid and pd.clientid = d.clientid
and pd.clientid = r.clientid
and (pd.activeStatus IS NOT NULL)
order by pd.clientlastname asc
This query takes a ton of time to execute. The sub selects are causing the slow down and I need to basically generate the list and include the last payment amount and last payment date from the receipts table.
Any help is greatly appreciated.