ProFox

profox@leafe.com

2704 discussions

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

25 Jun '18

You just need to change the first line of the query to this: SELECT COUNT(Status.ConFK) AS ConsCount, SUM(LastHours.AppHours) AS HoursSum; Frank. Frank Cazabon On 22/06/2018 01:04 PM, Ken Dibble wrote: > >> Maybe this will do what you want: >> >> >> SELECT Status.ConFK, LastHours.AppHours; >> 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 ; >> AND Type = "Approved"; >> LEFT JOIN (SELECT Hours.ConFK, Hours.AppHours; >> FROM Hours; >> INNER JOIN (SELECT ConFK, MAX(date) AS MaxHoursDate; >> FROM Hours; >> Â GROUP BY ConFK) MH ; >> Â ON Hours.ConFK = MH.ConFK AND Hours.Date = >> MH.MaxHoursDate ) LastHours ; >> ON Status.ConFK = LastHours.ConFK > > Yes, using the data I provided (and which you assembled into tables), > this gets: > > CONFK APPHOURS > 1 .NULL. > 3 12 > > The query itself doesn't sum anything, but I assume I can convert > NULLs to 0 and sum the APPHOURS column, and use _TALLY or RECCOUNT() > to provide a total of approved Consumers. > > Thank you very much!!! > > Ken > [excessive quoting removed by server]

2 1

[NF] Cloud discovery meetings held this week
by Stephen Russell 23 Jun '18

23 Jun '18

At the end of the third and final day of meeting with our ERP Consultants, our company realized that working in the cloud could be good. They keep version(s) of our applications as well as update data as needed to comply with that update. This is what we are looking forward to accomplishing by going to the cloud. Over the days we found that versions of the various software(s) that they have sold us do not push to the cloud easily or never will. Some of those software(s) deal with automation and they differ between plants of ours depending on the level of automation done at that plant. We were told about our data being available via accessing our Data Lake but that concept is still not out of alpha/build/test as of yet. Pulling the data from here is where a cost factor. We need this data to continue reporting to the company even though there is a new reporting environment, for us via version upgrade, that we have to learn how to use effectively. Other software we use will not be in the cloud initially but is planned to get there in the future. One of those is the Bill of Materials another is the EDI just to name two of many. We do not have a quote as of yet on how much this will cost because they were only getting actual facts on what needed to be done. OTTOMH I am guessing they say 12-18 months at 1 million dollars to be heavily discounted because we are the ALPHA site dong this with them. In reality, it will take close to 3 years and be in excess of 2 million before discounting or bill refusals. Will the executives be behind IT going down this road is the number one question and that will be a Fall 2018 decision for a 2019 project? -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---

5 5

Fun with US sales tax
by Richard Kaye 22 Jun '18

22 Jun '18

Our wondrous Supreme Court has opened the Pandora’s Sales Tax box with today’s decision to allow states to require retailers with no physical presence in a particular state to collect sales tax for that state. Leaving aside any thoughts on the wisdom of this decision, which I have not looked at, I’d like to call on the collective wisdom of those who deal with invoicing to find out if any of you are using some sort of service provider/API to retrieve tax rates, presumably by submitting the address of the buyer to some kind of web API. For our non-US ProFox friends, you can just sit back and laugh. 😊 TIA -- rk

4 9

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

22 Jun '18

Did you run it as part of the first set of code that I gave you? It runs here with no errors in VFP9 with the latest hot fix and SPs. ;) What error is it giving you? Maybe a line split somewhere due to the email formatting? This is the full code: CREATE CURSOR Consumers; (ConPK int) INSERT INTO consumers (ConPK ) VALUES (1) INSERT INTO consumers (ConPK ) VALUES (2) INSERT INTO consumers (ConPK ) VALUES (3) *!* 1 *!* 2 *!* 3 *!* Status 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) *!* PK,ConFK,,Date,,AppHours 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 ) SELECT Status.ConFK, LastHours.AppHours; 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 ; AND Type = "Approved"; LEFT JOIN (SELECT Hours.ConFK, Hours.AppHours; FROM Hours; INNER JOIN (SELECT ConFK, MAX(date) AS MaxHoursDate; FROM Hours; GROUP BY ConFK) MH ; ON Hours.ConFK = MH.ConFK AND Hours.Date = MH.MaxHoursDate ) LastHours ; ON STatus.ConFK = LastHours.ConFK Frank. Frank Cazabon On 22/06/2018 12:21 PM, Ken Dibble wrote: > >> SELECT Consumer.*, Status.*, Hours.AppHours >> FROM Consumer >> JOIN Status on Consumer.ConPK=Status.ConFK >> LEFT OUTER 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 = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE >> Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK) > > I actually can't get this to run without errors. Interpolating, I > **think** you meant: > >> SELECT Consumer.*, Status.*, Hours.AppHours >> FROM Consumer >> JOIN Status on Consumer.ConPK=Status.ConFK >> LEFT OUTER 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" GROUP BY >> Stat2.ConFK) >> AND Hours.Date = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE >> Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK) > > However, I get "Function missing )" on the last subquery on this this, > and neither intellisense nor I can find any unmatched parens. > > Also, the first subquery is grouping on the Status table FK, while the > second subquery groups on the Hours table PK. I think they should both > group on the same key, but I don't understand which one that should be. > > Thanks. > > Ken > > [excessive quoting removed by server]

2 2

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

22 Jun '18

Maybe this will do what you want: SELECT Status.ConFK, LastHours.AppHours; 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 ; AND Type = "Approved"; LEFT JOIN (SELECT Hours.ConFK, Hours.AppHours; FROM Hours; INNER JOIN (SELECT ConFK, MAX(date) AS MaxHoursDate; FROM Hours; GROUP BY ConFK) MH ; ON Hours.ConFK = MH.ConFK AND Hours.Date = MH.MaxHoursDate ) LastHours ; ON Status.ConFK = LastHours.ConFK 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]

2 1

RE: Query Involving Sums and Two Most Recent Dates
by Ken Dibble 22 Jun '18

22 Jun '18

>Personally I avoid _TALLY since so many commands & functions in VFP >will change it. I don't think there's any danger of it getting changed if I store the contents of _TALLY to a local variable immediately after running the query, which is what I always do. I think RECCOUNT() moves the record pointer, which might not be desirable in some circumstances but wouldn't make a difference in my case. Ken Dibble www.stic-cil.org

1 0

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

22 Jun '18

>SELECT Consumer.*, Status.*, Hours.AppHours >FROM Consumer >JOIN Status on Consumer.ConPK=Status.ConFK >LEFT OUTER 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 = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE >Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK) I actually can't get this to run without errors. Interpolating, I **think** you meant: >SELECT Consumer.*, Status.*, Hours.AppHours >FROM Consumer >JOIN Status on Consumer.ConPK=Status.ConFK >LEFT OUTER 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" GROUP BY >Stat2.ConFK) >AND Hours.Date = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE >Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK) However, I get "Function missing )" on the last subquery on this this, and neither intellisense nor I can find any unmatched parens. Also, the first subquery is grouping on the Status table FK, while the second subquery groups on the Hours table PK. I think they should both group on the same key, but I don't understand which one that should be. Thanks. Ken

1 0

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

22 Jun '18

> > 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. > >So, can a Consumer be both Approved and Pending? > >By your logic, if a Company Name on a company record could change >(when Acme Widgets is taken over by Apex Amalgamated) that should be >in a child table, with a date range for when each name is effective. > >If the status is always only one value at that point in time, but may >change over time, you might store the CURRENT STATUS in the Consumer >table, and an history (or Audit Trail) of updates to that status in a >Status Audit Trail table. > >It's an issue of modeling the dimension of time in your database >schema. Typically, the current status is what you want to check when >you query the tables: how much stuff is in stock, how many projects >are pending, etc. all come with the implicit requirement of "at this >time." But other reports would question "how did this inventory vary >over time" which would trace history/audit-trail records. This is probably the best way for me to think about this. It may or may not be needless to say that it's waaaay too late to change the design of the database and related code in such a fundamental way--if I concluded that, in my specific case, it would be the best way to go. I've given you one example of a problem that occurs in several different modules of the system, each with its own set of parent records and child tables, many of which have "expiration" or "supercession" (is that a word?) dates. This isn't sales with invoices and orders; it's a quasi-medical case management/service/process tracking and documentation system. Users need to look at the "historical" information frequently. I did actually write a generic method for my reporting system a few years ago that can accept a few parms and take the long way around (multiple queries and sorting tricks) to produce these kinds of results regardless of the tables being processed. It works for a reasonably large subset of these problems, but this particular case, a report asking for a couple of totals rather than a list of names and related items, is sufficiently different and unusual in my case to make me think about doing something different. Unfortunately, I can't do something THAT different. > > 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. > >Only if the two things mean the same thing. The Consumer record stores >the current state. The Status Audit table stores the history of state >changes. They may have similar names, but are in fact different pieces >of information. > >Consumer: Approved is the current status of the process >Status table: Approved, {^2018-6-21) is the date the process got that status. I think I have to respectfully disagree here. Perhaps it's only the (undiagnosed) Aspergerishness in me. But I am an entirely self-taught developer who does not really understand set theory very well. A program that I produced in the early 1990s to perform some of the tasks the current system handles had a single, very large table that stored everything. At the time it was required to report whether a specific service had been delivered to a specific consumer at least once per quarter. So the table had one row per consumer and a field for each service and each quarter: S1Q1, S2Q1... S1Q3... etc. There was at that time no need to maintain a historical record of services; at the end of each reporting year the service fields were purged and we started again. Neverthess, I think everyone would say this was a terrible design. I don't think that adding a different effective date to a piece of data that is otherwise identical to other pieces of data makes it a "different thing" in normalization terms. I can see that in some cases it would be more convenient to do so. But if instead of two or three dated items per parent, what if I had fifteen or twenty? My system provides a utility function to superusers; they can add new Status options without requiring me to do any programming. And they frequently do this. Following your suggestion, I would have to do programming to modify the parent tables every time they did that. (I can't automate it; I have to boot everybody out of the system to modify tables that are constantly in use.) You might say, "Oh. That's new information." However, If I had given you 3000 words explaining the full complexity of the system in advance, you would have responded "tl;dr". I am always struggling to understand the principles behind these things, but every time I think I've found one somebody comes along and tells me, no, that's not it. It makes everything, in the end, seem terribly ad hoc, and that makes it really difficult for me to apply what I've learned to new situations. Still, I deeply appreciate the time you've given to me and the opportunity to learn something that you always provide. >If changes happen, the data schema might have to change. That's why >the database manufacturers gave us that capability. And this can be severely screwed up, causing major damage. While wearing one of my other hats on this job, for the last three weeks I have been struggling with a company that provides Medicaid billing software. A user had a question. The company refused to answer the question until we installed the latest version. We always approach this with trepidation, because there are always problems. But I installed the update. The process went without a hitch and all of the tests on the data afterwards checked out. However, as the users began working with the system, they began encountering major slowdowns, and if more than one was in the system at the same time, it would crash with "deadlock" errors. Company support suggested that we had hit the wall on the SQL Server Express we were using and suggested we purchase the full version of SQL Server. So I spent $1300 to do that, and installed it. Even though the amount of data that was being processed before the update--at least in terms of what we had entered--had not changed after the update. The updating process required, as it always does, a lengthy "conversion" of the database (it took nearly an hour on a Windows 2012 server VM with 24 GB of RAM and 4 very beefy Xeon processors). There's a detailed list of the changes made in each version. They added a bunch of new fields and tables, among other things, and probably moved things around in the manner that you suggest is necessary and routine. As a result, the size of the database was significantly bloated--and if I had to guess, they did not thoroughly test the updates with datasets the size of ours (which is, apparently, unusually large for them), to ensure that everything was optimized. The conversion process may even have corrupted the data. Since then we have been arguing with them daily, and they keep trying to tell me that my hardware capacity (which exceeds their minimum specifications by a huge margin), or the out-of-the-box SQL Server configuration (which I do not know how to change and for which I would have to hire a consultant), had suddenly become too weak for the system between the time they took a version of the database that was working fine, and converted it--without us adding any data--to a much larger database that does not work fine. We are now facing a serious cash flow problem because we can't get enough billing out on a daily basis. > > 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. > >New info. Welcome to my world. The "customer" for this particular module is extremely inarticulate. I always go through multiple iterations with her before I finally get to giving her what she really wants. I've been working with her for a long time; she is not getting any better at telling me at the beginning exactly what I should do. Here is the request from her that initiated this process, verbatim: "Can you create a report so I can determine how many people are currently approved for services and the total amount of units the people are approved for?" Having learned through experience that talking to her about it would not produce significantly greater clarity, I just went ahead and started developing some code that would appear to answer her question. I really wanted to just run an ad hoc query in the development environment without constructing an interface or integrating it into the system so I could show her what answer my interpretation of the question would provide. I knew she would then say, "No.. it's not really that..." and we would be off to the races. As for the code: I will test yours and Frank's solutions and respond later today. Thank you very much!! 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 2:22 PM, Ken Dibble <krdibble(a)stny.rr.com> wrote: > > 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. So, can a Consumer be both Approved and Pending? By your logic, if a Company Name on a company record could change (when Acme Widgets is taken over by Apex Amalgamated) that should be in a child table, with a date range for when each name is effective. If the status is always only one value at that point in time, but may change over time, you might store the CURRENT STATUS in the Consumer table, and an history (or Audit Trail) of updates to that status in a Status Audit Trail table. It's an issue of modeling the dimension of time in your database schema. Typically, the current status is what you want to check when you query the tables: how much stuff is in stock, how many projects are pending, etc. all come with the implicit requirement of "at this time." But other reports would question "how did this inventory vary over time" which would trace history/audit-trail records. > 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. Only if the two things mean the same thing. The Consumer record stores the current state. The Status Audit table stores the history of state changes. They may have similar names, but are in fact different pieces of information. Consumer: Approved is the current status of the process Status table: Approved, {^2018-6-21) is the date the process got that status. > 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, That's a "yeah, but what if" hypothetical. By that rationale, all data schemas are out of date, and we should just use document databases. Let's skip that slippery slope. If changes happen, the data schema might have to change. That's why the database manufacturers gave us that capability. > 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. Solving hardware problems with bad software is rarely a win. Backup, migrate, validate, rollback or commit. >> > 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. New info. SELECT Consumer.*, Status.*, Hours.AppHours FROM Consumer JOIN Status on Consumer.ConPK=Status.ConFK LEFT OUTER 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 = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK) Where Hours.AppHours come out as NULL, you'll want to substitute zero to get Approved Consumers who have no Hours record posted. -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com

1 0

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

21 Jun '18

Yeah, that's worth testing, and GMail doesn't tell me that :) I'm guessing _TALLY should be the number, but you can always check RECCOUNT() of the query result. On Thu, Jun 21, 2018 at 1:32 PM, Ken Dibble <krdibble(a)stny.rr.com> wrote: > 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]

2 1

Jump to page:

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

ProFox