On Thu, Jun 21, 2018 at 1:27 PM, Ken Dibble <[email protected]> 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 -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ 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/CACW6n4tDYxxTAURB6hXM7z5Xtf_LcTv=5-dvkko6wemqiwd...@mail.gmail.com ** 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.
