I should have realized that from your example solution. I *ASSuMEd* we
were totalling hours. My bad.
SELECT Consumer.*, Status.*, Hours.AppHours
FROM Consumer
JOIN Status on Consumer.ConPK=Status.ConFK
JOIN Hours on Consumer.ConPK=Hours.ConFK
WHERE Status.Date =
(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK)
AND Status.Type = "Approved"
AND Hours.Date = Status.Date
Better, but there is no guarantee that Hours.Date will equal Status.Date.
The results have to total the most recent hours records belonging to
Consumers whose most recent Status is "Approved".
And my testing indicates that a GROUP BY clause is required somewhere.
I'm wondering if this will turn out to be np-hard and my original
multi-query approach is the only way to do this.
Thanks.
Ken
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
