You just need to change the first line of the query to this:
SELECT COUNT(Status.ConFK) AS ConsCount, SUM(LastHours.AppHours) AS HoursSum;
Frank.
Frank Cazabon
On 22/06/2018 01:04 PM, Ken Dibble wrote:
Maybe this will do what you want:
SELECT Status.ConFK, LastHours.AppHours; FROM Status ; INNER JOIN (SELECT ConFK, MAX(Date) AS MaxDate; FROM Status; GROUP BY ConFK) M ON Status.ConFK = M.ConFK AND Status.Date = M.MaxDate ; AND Type = "Approved"; LEFT JOIN (SELECT Hours.ConFK, Hours.AppHours; FROM Hours; INNER JOIN (SELECT ConFK, MAX(date) AS MaxHoursDate; FROM Hours; Â GROUP BY ConFK) MH ; Â ON Hours.ConFK = MH.ConFK AND Hours.Date = MH.MaxHoursDate ) LastHours ; ON Status.ConFK = LastHours.ConFK
Yes, using the data I provided (and which you assembled into tables), this gets:
CONFK APPHOURS 1 .NULL. 3 12
The query itself doesn't sum anything, but I assume I can convert NULLs to 0 and sum the APPHOURS column, and use _TALLY or RECCOUNT() to provide a total of approved Consumers.
Thank you very much!!!
Ken
[excessive quoting removed by server]
Frank,
Just to close out this discussion:
The code you provided worked perfectly. I've also demonstrated that if the user wants results for Consumers with more than one option for Current Status (ie, those who are "approved" or "on hold"), I can simply add a UNION clause with the same query and that works just fine.
Thank you very much for this.
Ken
PS: The jumble below is what happens when people use Outlook's proprietary HTML code to send email to people who don't use Outlook. Sending in plain text fixes this problem.
Content-Transfer-Encoding: base64You just need to change the first line of the query to this:
SELECT COUNT(Status.ConFK) AS ConsCount, SUM(LastHours.AppHours) AS HoursSum;
Frank.
Frank Cazabon
On 22/06/2018 01:04 PM, Ken Dibble wrote:
Maybe this will do what you want:
£M1
PMÑ ÑÕ̹ ½¹,°1 ÍÑ!½ÕÈs.AppHours;
FROM Status ; INNER JOIN (SELECT ConFK, MAX(Date) AS MaxDate;0¨0¨0¨0¨0¨0¨0¨OM Status;
0¨0¨0¨0¨0¨0¨0¨ÔÕTHÛÛÊHHÓÝ]\ËConFK = M.ConFK AND Status.Date = KX^]HÂâ** AND Type = "Approved"; QÒS ÑSPÕÝ\ËÛÛK, Hours.AppHours;
0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨ÓHÝ\ÎÂ>> INNER JOIN (SELECT ConFK, MAX(date) AS MaxHoursDate; 0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨ FROM Hours;
0¨0¨0¨0¨0¨0¨0¨0¨0¨0 Â GROUP BY ConFK) MH ;
0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0¨0à°¨0¨0¨0¨0¨ÓÀurs.ConFK = MH.ConFK AND Hours.Date =
MH.MaxHoursDate ) LastHours ; ON Status.ConFK = LastHours.ConFK
Y\Ë\Ú[ÈH]HHÝYY [ which you assembled into tables),
this gets:
CONFK APPHOURS 1 .NULL. 3 12
H]Y\H]Ù[Ù\Àn't sum anything, but I assume I can convert
NULLs to 0 and sum the APPHOURS column, and use _TALLY or RECCOUNT() to provide a total of approved Consumers.
[À you very much!!! Ù[¥¶W6W76fRV÷Fær&VÖ÷fVB' server]
[excessive quoting removed by server]
-
Frank Cazabon -
Ken Dibble