ProFox

profox@leafe.com

2704 discussions

Re: Query Involving Sums and Two Most Recent Dates
by Frank Cazabon 21 Jun '18

21 Jun '18

I mean specifically, which records (consumer PK and apphours) would you expect in your results? Example: 2, 12 and 3, 14 On 21 June 2018 14:28:10 GMT-04:00, Ken Dibble <krdibble(a)stny.rr.com> 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]

2 1

Re: Query Involving Sums and Two Most Recent Dates
by Frank Cazabon 21 Jun '18

21 Jun '18

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]

1 0

Re: Query Involving Sums and Two Most Recent Dates
by Frank Cazabon 21 Jun '18

21 Jun '18

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 > > > [excessive quoting removed by server]

2 2

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

21 Jun '18

Responses in-line: >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. Interesting. I've always just followed the principle that if a parent can own more than one value for a particular item, you put that item in a child record, and keep the child records in a separate table and JOIN it as needed. I thought that was a requirement for normalization. It would seem to engender less upkeep than creating a need to update something in the parent record whenever you change something in the child record. Also, if a need develops for the parent to own more than one of something else that was not part of the original design, something which would require yet another child table, I would then also have to modify the parent table to add a "most recent" field for that. Modifying existing tables containing data seems more risky than simply adding empty tables--especially when I have to do it in a completely automated way for remote locations whose hardware I don't trust. > > 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. I need a total of the Hours stored to all of the LAST hours records that are owned by Consumers whose LAST status record contains "Approved" in the "type" field. Bear in mind that a Consumer may not own any records in the Hours table at all. > > And my testing indicates that a GROUP BY clause is required somewhere. > > > >It's in there, with the sub-select with the MAX Yes it is. Sorry that I missed it. Thanks for all of your help. 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 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

Jump to page:

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

ProFox