Yeah, that's worth testing, and GMail doesn't tell me that :) I'm guessing _TALLY should be the number, but you can always check RECCOUNT() of the query result.
On Thu, Jun 21, 2018 at 1:32 PM, Ken Dibble krdibble@stny.rr.com wrote:
Er... and while still rendering a total number of Consumers whose hours were totalled--at least via _TALLY if nothing else.
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.
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
[excessive quoting removed by server]
Personally I avoid _TALLY since so many commands & functions in VFP will change it.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Ted Roche Sent: Thursday, June 21, 2018 1:56 PM To: profoxtech@leafe.com Subject: Re: Query Involving Sums and Two Most Recent Dates
Yeah, that's worth testing, and GMail doesn't tell me that :) I'm guessing _TALLY should be the number, but you can always check RECCOUNT() of the query result.
On Thu, Jun 21, 2018 at 1:32 PM, Ken Dibble krdibble@stny.rr.com wrote:
Er... and while still rendering a total number of Consumers whose hours were totalled--at least via _TALLY if nothing else.
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.
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
[excessive quoting removed by server]
-
Richard Kaye -
Ted Roche