Hi... bit of an odd one. I have a query that works, but I'm sure it's not right.
For the purposes of this query, I've got three tables:
Each Greeting is tied to a Charity, as is each CharityPayment.
I want to get a sum of all the Greetings on behalf of a Charity since their last payment date, and the payment date itself.
Here's what I've got. Like I said - it seems to work, and pull out the right-looking values. But I've got to do two subqueries in it to drop the last payment date into the WHERE clause - it just seems a hideously inelegant way of using a value that I've already got in the SELECT clause as "MAX(cp.paymentToDate) AS lastPaymentToDate". Is is right, or is there a better way?
COUNT(g.idGreetings) AS totalGreetings,
MAX(cp.paymentToDate) AS lastPaymentToDate
FROM Greetings g
INNER JOIN Charities c ON g.idCharities = c.idCharities
LEFT JOIN CharityPayments cp ON cp.idCharities = c.idCharities
(g.sendDate > (
SELECT MAX(cp2.paymentToDate) AS lastPaymentToDate
FROM CharityPayments cp2
WHERE cp2.idCharities = c.idCharities
) OR (
SELECT MAX(cp3.paymentToDate) AS lastPaymentToDate
FROM CharityPayments cp3
WHERE cp3.idCharities = c.idCharities
) IS NULL)
GROUP BY g.idCharities
ORDER BY c.charityName;
if it works, let me know if there's any part you don't understand
, COUNT(g.idGreetings) AS totalGreetings
FROM Charities AS c
JOIN Greetings AS g
ON g.idCharities = c.idCharities
JOIN ( SELECT idCharities
, MAX(paymentToDate) AS lastPaymentToDate
BY idCharities ) AS p
ON p.idCharities = c.idCharities
WHERE g.sendDate > COALESCE(p.lastPaymentToDate,'1900-01-01')
It's not working right. It's coming out with the wrong numbers.
Currently, there is only one Charity with any Payments logged against it. There are two payments logged for that Charity. Since the most recent of these payment dates, there have been 12 Greetings for that Charity.
As it stands, the query correctly pulls out one row for that Charity, correctly displays the latest of the two payment dates, and claims it's found not 12 but 24 Greetings.
If I amend the query with:
GROUP BY g.idCharities, cp.idCharityPayments
then I'm back down to 12 Greetings - but it shows two rows for the Charity.
How can I get it to display one row, with the latest payment date, and only SUM the Greetings once?
Sorry - didn't see your original reply when I posted the second time. Your query pulls out exactly the right data - I am once again massively indebted to you. (And looking up the "Coalesce" reference page as I type...)