On Thu, Jun 21, 2018 at 1:27 PM, Ken Dibble krdibble@stny.rr.com wrote:

Better, but there is no guarantee that Hours.Date will equal Status.Date.

...

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.

No, it's just a little quirky due to your design choices. Typically, I would have a "current status" value in the Consumer record that's an FK into the Status history table.

A common problem with data modeling is determining if your system is a reflection of the current version of the system, or the history of the states of the system, or a mix of both.

The results have to total the most recent hours records belonging to Consumers whose most recent Status is "Approved".

I think your description of the hours result is a little ambiguous, and since I've guessed wrong twice, I'll stop now. ;)

Are you SUM'ming Hours records for the Consumer SINCE their status became approved, or are you picking the LAST Hours record for Consumers currently Approved? Either is easy. Ish.

And my testing indicates that a GROUP BY clause is required somewhere.

It's in there, with the sub-select with the MAX

...

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