ProFox

profox@leafe.com

2704 discussions

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

[ADMIN] Stuck inside the Filter (with the Memphis blues again)
by Ken Dibble 21 Jun '18

21 Jun '18

... Sorry, Bob... Ed, I sent a message concerning SQL queries about a half hour ago. It hasn't shown up in my email, and it's not in the archive. In all of my previous experience, 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. Can you please check it? Thanks. Ken Dibble www.stic-cil.org

1 0

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

21 Jun '18

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

1 0

FoxPro for Windows: Page Layout
by Desmond Lloyd 20 Jun '18

20 Jun '18

That's right, FoxPro for Windows! Have this old application I am supporting. One report will not print in the correct orientation. It should be landscape, but prints in portrait. Have tried what I thought would be the "normal" things to do. 1.) Checked it in the report designer 2.) "Emptied" tag1 and tag2. All to no joy.... Any suggestions... Regards/TIA, Desmond --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---

3 2

Database design
by mbsoftwaresolutions＠mbsoftwaresolutions.com 19 Jun '18

19 Jun '18

(VFP9SP2 app with MySQL backend) Some years ago I had created a solution where I had split the main data table into 5 separate tables, each with a 1:1 relationship. I kept the commonly held fields in the table, and moved others off like so: - Main Job table - Job Address table (job site address) - Job Dates table (key event dates for this job) - Job Notes table (all text/memo fields, with key field of course) - Job People table (folks assigned to the job) On review, I think this just made more work and I should consolidate all of these fields inside the single Job table. Your thoughts on either design approach? tia, --Mike

7 13

Feel true performance with Viagra.
by Claudio Dubois 18 Jun '18

18 Jun '18

body, .mainTable { height:100% !important; width:100% !important; margin:0; padding:0; } img, a img { border:0; outline:none; text-decoration:none; } .imageFix { display:block; } table, td { border-collapse:collapse; mso-table-lspace:0pt; mso-table-rspace:0pt;} p {margin:0; padding:0; margin-bottom:0;} .ReadMsgBody{width:100%;} .ExternalClass{width:100%;} .ExternalClass, .ExternalClass p, .ExternalClass span, .ExternalClass font, .ExternalClass td, .ExternalClass div{line-height:100%;} img{-ms-interpolation-mode: bicubic;} body, table, td, p, a, li, blockquote{-ms-text-size-adjust:100%; -webkit-text-size-adjust:100%;} 96   If you can't read this email, please view it online http://dark.thefamilymart.ru   Good afternoon! Our store is pleased to offer you our product list with delivery service in Europe, the United States and Canada. You can buy anti-acidity, anti-depressant, antifungals, antifungals, blood pressure, anti-allergy/asthma, antiviral, diabetes medication, herpes medication, antibiotics and other various products. Significant discounts when purchasing    ___Hot Offer___ If you want more information about our privacy policy, please visit this page. If you no longer wish to receive these emails, simply click on the following link Unsubscribe. ©2017 Company. All rights reserved.   --- StripMime Report -- processed MIME parts --- text/html (html body -- converted) ---

1 0

[NF] Best Practices for collaborative sharing of information
by mbsoftwaresolutions＠mbsoftwaresolutions.com 15 Jun '18

15 Jun '18

The Corporate gig has tasked me with looking into a way for several similar organizations to communicate and share with each other things are the related to the success of all of them; a collaborative sharing of ideas, best practices, information, etc. to help the (healthcare) product as a whole. I have advised against a listserve as I don't feel that's friendly enough for sharing materials/files/stories. I'm believing a forum would be best. Would appreciate your thoughts. Thanks, --Mike

11 28

Jump to page:

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

ProFox