Sorry, left off my attempt as I realised I hadn't understood the requirements properly
On 21 June 2018 14:21:13 GMT-04:00, Frank Cazabon <[email protected]> wrote: >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 >> >> >> >> _______________________________________________ >> 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. >> >> Report [OT] Abuse: >> >http://leafe.com/reportAbuse/[email protected] >> -- Frank Cazabon Sent from my phone. Please excuse my brevity. --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ 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.
