New subject: Query Involving Sums and Two Most Recent Dates

21 Jun 2018


      What's your expected output from the data you presented?
To make it a little easier for people to try to help, here's some code 
to build up the data and my attempt at what I've understood as your 
requirements:
CREATE CURSOR Consumers;
(ConPK int)
INSERT INTO consumers (ConPK ) VALUES (1)
INSERT INTO consumers (ConPK ) VALUES (2)
INSERT INTO consumers (ConPK ) VALUES  (3)
CREATE CURSOR Status;
(StatPK int,;
ConFK Int,;
Date date,;
Type c(10))
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES 
(1,1,{^2018/1/15},"Pending")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES 
(2,1,{^2018/4/15},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES 
(3,2,{^2018/6/1},"Closed")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES 
(4,2,{^2018/4/15},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES 
(5,3,{^2017/11/22},"Pending")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES 
(6,3,{^2018/2/3},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES 
(7,3,{^2018/3/12},"Suspended")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES 
(8,3,{^2018/5/11},"Approved")
CREATE CURSOR Hours;
(HourPK int,;
conFK int,;
Date d,;
Apphours int)
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES 
(1,2,{^2017/7/3},10)
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES 
(2,2,{^2018/3/12},14)
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES 
(3,3,{^2018/4/1},20)
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES 
(4,3,{^2018/5/1},12 )
Frank.
Frank Cazabon
On 21/06/2018 10:31 AM, Ken Dibble wrote:
...
Hi Folks,
I have to admit that I've never been able to figure out how to get 
MAX() to work correctly in queries.
Given the following tables (simplified):
Consumers
PK
1
2
3
Status
PK    ConFK        Date        Type
1    1        {^2018/1/15}     Pending
2    1        {^2018/4/15}    Approved
3    2        {^2018/6/1}    Closed
4    2        {^2018/4/15}    Approved
5    3        {^2017/11/22}    Pending
6    3        {^2018/2/3}    Approved
7    3        {^2018/3/12}    Suspended
8    3        {^2018/5/11}    Approved
Hours
PK    ConFK        Date        AppHours
1    2        {^2017/7/3}    10
2    2        {^2018/3/12}    14
3    3        {^2018/4/1/}    20
4    3        {^2018/5/1}    12
Query: How many Consumers have a most recent Status of Approved, and 
what is the sum of those Consumers' Approved Hours?
Answer: 2 Approved Consumers, having a total of 12 Hours.
I realize it's probably two queries: one for the sum of Consumers, and 
one for the sum of Hours. However, I have been unable to figure out 
how to write a simple query to get either of those two values.
I have laborious code that does things like pulling in all of the 
Status records for each consumer and sorting them by date to find the 
most recent one, and then doing the same for Hours records, and then 
munging the data together into a final output cursor, and it works 
fine and is fast enough, but it just makes me crazy every time 
somebody wants a slightly different report along these lines.
It seems like there should be a way to do this more simply.
I am sure that I am missing something extremely obvious.
Thanks for any help.
Ken Dibble
www.stic-cil.org
[excessive quoting removed by server]

Re: Query Involving Sums and Two Most Recent Dates