Re: Query Involving Sums and Two Most Recent Dates

Mon, 25 Jun 2018 07:43:28 -0700 

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

PMхÑÕ̹
½¹,°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¨Ô“ÕT­HÛۑ’ÊHHӈÝ]\ˀConFK = M.ConFK AND Status.Date = ˆK“X^]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!!!
Ù[‚€¥¶W†6W76—fRV÷F­ær&VÖ÷fVB' server]


[excessive quoting removed by server]

_______________________________________________
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.

Reply via email to