ProFox June 2018

profox@leafe.com

38 participants
55 discussions

Re: Query Involving Sums and Two Most Recent Dates
by Ted Roche 21 Jun '18

21 Jun '18

On Thu, Jun 21, 2018 at 1:27 PM, Ken Dibble <krdibble(a)stny.rr.com> wrote: > > > > Better, but there is no guarantee that Hours.Date will equal Status.Date. > ... > I'm wondering if this will turn out to be np-hard and my original > multi-query approach is the only way to do this. > No, it's just a little quirky due to your design choices. Typically, I would have a "current status" value in the Consumer record that's an FK into the Status history table. A common problem with data modeling is determining if your system is a reflection of the current version of the system, or the history of the states of the system, or a mix of both. > The results have to total the most recent hours records belonging to > Consumers whose most recent Status is "Approved". I think your description of the hours result is a little ambiguous, and since I've guessed wrong twice, I'll stop now. ;) Are you SUM'ming Hours records for the Consumer SINCE their status became approved, or are you picking the LAST Hours record for Consumers currently Approved? Either is easy. Ish. > And my testing indicates that a GROUP BY clause is required somewhere. > It's in there, with the sub-select with the MAX >> SELECT Consumer.*, Status.*, Hours.AppHours >> FROM Consumer >> JOIN Status on Consumer.ConPK=Status.ConFK >> JOIN Hours on Consumer.ConPK=Hours.ConFK >> WHERE Status.Date = >> (SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE >> Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK) >> AND Status.Type = "Approved" >> AND Hours.Date = Status.Date -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com

1 0

Re: Query Involving Sums and Two Most Recent Dates
by Ken Dibble 21 Jun '18

21 Jun '18

Er... and while still rendering a total number of Consumers whose hours were totalled--at least via _TALLY if nothing else. >>I should have realized that from your example solution. I *ASSuMEd* we >>were totalling hours. My bad. >> >>SELECT Consumer.*, Status.*, Hours.AppHours >>FROM Consumer >>JOIN Status on Consumer.ConPK=Status.ConFK >>JOIN Hours on Consumer.ConPK=Hours.ConFK >>WHERE Status.Date = >>(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE >>Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK) >>AND Status.Type = "Approved" >>AND Hours.Date = Status.Date > >Better, but there is no guarantee that Hours.Date will equal Status.Date. > > >And my testing indicates that a GROUP BY clause is required somewhere. > >I'm wondering if this will turn out to be np-hard and my original >multi-query approach is the only way to do this. > >Thanks. > >Ken > [excessive quoting removed by server]

1 0

Re: Query Involving Sums and Two Most Recent Dates
by Ken Dibble 21 Jun '18

21 Jun '18

>I should have realized that from your example solution. I *ASSuMEd* we >were totalling hours. My bad. > >SELECT Consumer.*, Status.*, Hours.AppHours >FROM Consumer >JOIN Status on Consumer.ConPK=Status.ConFK >JOIN Hours on Consumer.ConPK=Hours.ConFK >WHERE Status.Date = >(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE >Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK) >AND Status.Type = "Approved" >AND Hours.Date = Status.Date Better, but there is no guarantee that Hours.Date will equal Status.Date. The results have to total the most recent hours records belonging to Consumers whose most recent Status is "Approved". And my testing indicates that a GROUP BY clause is required somewhere. I'm wondering if this will turn out to be np-hard and my original multi-query approach is the only way to do this. Thanks. Ken

1 0

Re: Query Involving Sums and Two Most Recent Dates
by Ted Roche 21 Jun '18

21 Jun '18

On Thu, Jun 21, 2018 at 12:22 PM, Ken Dibble <krdibble(a)stny.rr.com> wrote: > > Thank you, but I think I left out a requirement. The hours have to come from > the most recent Hours records as well. > I should have realized that from your example solution. I *ASSuMEd* we were totalling hours. My bad. SELECT Consumer.*, Status.*, Hours.AppHours FROM Consumer JOIN Status on Consumer.ConPK=Status.ConFK JOIN Hours on Consumer.ConPK=Hours.ConFK WHERE Status.Date = (SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK) AND Status.Type = "Approved" AND Hours.Date = Status.Date

1 0

Re: Query Involving Sums and Two Most Recent Dates
by Ken Dibble 21 Jun '18

21 Jun '18

>> >>SELECT Consumer.*, Status.*, SUM(Hours.AppHours) AS TotalHours >>FROM Consumer >>JOIN Status on Consumer.ConPK=Status.ConFK >>JOIN Hours on Consumer.ConPK=Hours.ConFK >>WHERE Status.Date = (SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE >>Stat2.ConFK = Consumer.ConPK) >> AND Status.Type = "Approved" > >Thank you, but I think I left out a requirement. The hours have to >come from the most recent Hours records as well. Also appears to require a GROUP BY clause. Thanks. Ken

1 0

Re: [ADMIN] Stuck inside the Filter (with the Memphis blues again)
by Ed Leafe 21 Jun '18

21 Jun '18

On Jun 21, 2018, at 10:07 AM, Ken Dibble <krdibble(a)stny.rr.com> wrote: > > Despite the convential wisdom about the vagaries of email, in all my previous experience with ProFox, this never happens unless the message was stuck in the filter. You called it. -- Ed Leafe

2 1

Re: Query Involving Sums and Two Most Recent Dates
by Ken Dibble 21 Jun '18

21 Jun '18

> >SELECT Consumer.*, Status.*, SUM(Hours.AppHours) AS TotalHours >FROM Consumer >JOIN Status on Consumer.ConPK=Status.ConFK >JOIN Hours on Consumer.ConPK=Hours.ConFK >WHERE Status.Date = (SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE >Stat2.ConFK = Consumer.ConPK) > AND Status.Type = "Approved" Thank you, but I think I left out a requirement. The hours have to come from the most recent Hours records as well. Ken

1 0

Re: Query Involving Sums and Two Most Recent Dates
by Ted Roche 21 Jun '18

21 Jun '18

On Thu, Jun 21, 2018 at 10:31 AM, Ken Dibble <krdibble(a)stny.rr.com> 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. Not at a machine with VFP, so I can't test this, but it looks good in Google Mail :) SELECT Consumer.*, Status.*, SUM(Hours.AppHours) AS TotalHours FROM Consumer JOIN Status on Consumer.ConPK=Status.ConFK JOIN Hours on Consumer.ConPK=Hours.ConFK WHERE Status.Date = (SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE Stat2.ConFK = Consumer.ConPK) AND Status.Type = "Approved" -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com

1 0

Re: Query Involving Sums and Two Most Recent Dates
by Peter Cushing 21 Jun '18

21 Jun '18

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]

1 0

Re: Query Involving Sums and Two Most Recent Dates
by Peter Cushing 21 Jun '18

21 Jun '18

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]

1 0

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

ProFox June 2018