MAX in WHERE clause?

Spudhead · #1 (**permalink**) 07-10-09, 07:29

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:
Greetings
Charities
CharityPayments

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?

Code:

SELECT
c.idCharities,
c.charityName,
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
WHERE (
  (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;

r937 · #2 (**permalink**) 07-10-09, 07:58

let me know if this works

if it works, let me know if there's any part you don't understand

Code:

SELECT c.idCharities
     , c.charityName
     , COUNT(g.idGreetings) AS totalGreetings    
     , p.lastPaymentToDate
  FROM Charities AS c
INNER 
  JOIN Greetings AS g 
    ON g.idCharities = c.idCharities
LEFT OUTER
  JOIN ( SELECT idCharities
              , MAX(paymentToDate) AS lastPaymentToDate
           FROM CharityPayments 
         GROUP
             BY idCharities ) AS p
    ON p.idCharities = c.idCharities 
 WHERE g.sendDate > COALESCE(p.lastPaymentToDate,'1900-01-01')
GROUP 
    BY c.charityName

Spudhead · #3 (**permalink**) 07-10-09, 08:09

Ok, scrub that.

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?

r937 · #4 (**permalink**) 07-10-09, 08:14

script out the data for me please

Spudhead · #5 (**permalink**) 07-10-09, 08:54

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...)

Internet Services

Web Development

Digital Marketing

Consumer Tech