Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query Problem

Author

Topic

Lea Ibbs
Starting Member

3 Posts

Posted - 2008-03-22 : 05:33:18

I am trying to set up a query to select PaymentDate, CustSurname, CustForename, AgentName showing only the last record for each customer if the last payment is over 7 days and also include customers with no payment.

the table structure is:

tblLoans
LoanID (PK)
CustID (FK)
AgentID(FK)

tblPayments
PaymentID (PK)
PaymentDate
LoanID (FK)

tblAgents
AgentID (PK)
AgentName

tblCustomers
CustID (PK)
CustSurname
Custforename

I have tried the code as below: but it now gives me every payment for the defaulters as opposed to just the last defaulted payment. Can you shed any light on what I have done wrong.

select b.PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from tblCustomers a, tblPayments b, tblAgents c, tblLoans d
where d.LoanID = b.LoanID and d.CustID = a.CustID and d.AgentID = c.AgentID
and (DATE()-(SELECT max(PaymentDate) from tblPayments f where f.LoanID=d.LoanID ))>7
UNION select '' as PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from
tblCustomers a, tblLoans d, tblAgents c
where a.CustID = d.LoanID
and c.AgentID = d.AgentID
and (select count(*) from tblPayments where tblPayments.LoanID = d.LoanID) = 0;

sqlleaf
Starting Member

3 Posts

Posted - 2008-04-18 : 03:42:21

Hi. Would like to share some ideas here for your consideration.

The second part of your SQL looks ok:
UNION select '' as PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from
tblCustomers a, tblLoans d, tblAgents c
where a.CustID = d.LoanID
and c.AgentID = d.AgentID
and (select count(*) from tblPayments where tblPayments.LoanID = d.LoanID) = 0;

For the first part, I think it selects all the payments that defaulted (over 7 days), so there could be multiple for a single customer.

The following may eliminate payments defaulting on an earlier date, but if you have 2 payments with the same payment date, which is over 7 days, both will still be selected, and you need to fine-tune further:

SELECT b.PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
FROM tblCustomers AS a, tblPayments AS b, tblAgents AS c, tblLoans AS d,
[SELECT CustID, max(PaymentDate) AS MaxPaymentDate
FROM tblPayments AS f, tblLoans AS g
WHERE f.LoanID=g.LoanID
GROUP BY CustID
]. AS maxp
WHERE d.LoanID = b.LoanID and d.CustID = a.CustID and d.AgentID = c.AgentID
and d.CustID = maxp.CustID
and b.PaymentDate = maxp.MaxPaymentDate
and (DATE()-maxp.MaxPaymentDate)>7
UNION select '' as PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from
tblCustomers a, tblLoans d, tblAgents c
where a.CustID = d.LoanID
and c.AgentID = d.AgentID
and (select count(*) from tblPayments where tblPayments.LoanID = d.LoanID) = 0;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources