Hi Ken,

You can get them both with 1 query. Pressed send too soon on the last reply.

select status.pk,count(*) as nCust,sum(apphours) as nApphours ; from status,hours ;    where status.pk = hours.pk and status.type = 'Approved' group by 1 ;   into cursor curres

HTH

Peter

Peter Cushing IT Department WHISPERING SMITH

On 21/06/2018 15:31, 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]