Hi Ken,
You can get them both with 1 query.
select 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]
_______________________________________________
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/[email protected]
** 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.
