Last Payment(s) received SQL?

  • Thread starter Thread starter Kevin Witty
  • Start date Start date
K

Kevin Witty

I have a table which has ClientCode, PaymentDate and PaymentAmt. There may
be multiple payments for a client on one date. I'm damned if I can come up
with the query which will show the last date each client made a payment on,
and the total of his payments on that date. Can anyone help?

Thanks,

Kevin
 
AhHah! Got it: it takes 3 queries: One groups by ClientCode, PmtRcvdDate
and sums PmtAmount. The second takes that query and groups by ClientCode
with a max of PmtRcvdDate. The third takes the second query and relates it
back to the first on ClientCode and PmtRcvdDate, and groups on ClientCode,
PmtRcvdDate and PmtAmount.

Man, do I miss old DataEase for DOS sometimes!
 
Kevin Witty said:
AhHah! Got it: it takes 3 queries: One groups by ClientCode,
PmtRcvdDate and sums PmtAmount. The second takes that query and
groups by ClientCode with a max of PmtRcvdDate. The third takes the
second query and relates it back to the first on ClientCode and
PmtRcvdDate, and groups on ClientCode, PmtRcvdDate and PmtAmount.

Man, do I miss old DataEase for DOS sometimes!
Click to expand...

How about one query, using a subquery? Try this:

SELECT
ClientCode,
PaymentDate AS LastPaymentDate,
Sum(PaymentAmount) AS AmountPaid
FROM Payments
WHERE
PaymentDate=
(SELECT Max(T.PaymentDate)
FROM Payments T
WHERE T.ClientCode = Payments.ClientCode)
GROUP BY
ClientCode,
PaymentDate;
 
Back
Top