For example, this query will bring out the 2 consumers whose last status was approved:
SELECT Status.ConFK; FROM Status ; INNER JOIN (SELECT ConFK, MAX(Date) AS MaxDate; FROM Status; GROUP BY ConFK) M ON Status.ConFK = M.ConFK AND Status.Date = M.MaxDate ; WHERE Type = "Approved"
That is 1 & 3
What records from the Hours table will give you the total of 12? Is it just the one where HourPK = 4 and ConFK = 3 (since this is the latest one for consumer 3 and there are no records for consumer 1 in the hours table)?
Frank.
Frank Cazabon
On 21/06/2018 02:28 PM, Ken Dibble wrote:
At 02:21 PM 6/21/2018, you wrote:
What's your expected output from the data you presented?
Answer: 2 Approved Consumers, having a total of 12 Hours.
I did include that in my original post.
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:
[snip]
Thank you for doing that. Wow, I'm sorry I omitted that. I usually do that when I ask for help with queries.
Ken
[excessive quoting removed by server]