help with calculation in SELECT

[Judi201]

Hi!

I am trying to create a cursor for a report using SELECT and doing some calculations to print on the report. I can usually recall data as I need it but need some help on the calculations. I have made it work with the following code but I am sure some of you could show me a much better way. I would appreciate any suggestions to improve.

SELECT orders.jobsnumb,orders.ordrnumb, orders.partnumb, orders.custno, ;
			customer.bname, customer.custno,orders.pgood, orders.pgwip, ;
			orders.purwght, ;
			(pbad1+pbad2+pbad3+pbad4+pbad5+pbad6+pbad7+pbad8+pbad9+pbad10+;
			pbad11+pbad12+pbad13+pbad14+pbad15+pbad16+pbad17+pbad17+pbad19+pbad20+;
			pbad21+pbad22+pbad23+pbad24+pbad25+pbad26+pbad27+pbad27+pbad29+pbad30);
			AS pbad ;
			FROM  company!orders LEFT OUTER JOIN company!customer ;
			ON orders.custno = customer.custno ;
			WHERE &lcSortBy >= THISFORM.txtBegin.VALUE AND ;
			&lcSortBy <=THISFORM.txtEnd.VALUE ;
			INTO CURSOR temp1 ;
			ORDER BY &lcSortBy ;
			GROUP BY &lcSortBy
		SELECT *, ;
			SUM(pgood+pbad) AS pmade ;
			FROM temp1 ;
			INTO CURSOR temp2 ;
			ORDER BY &lcSortBy ;
			GROUP BY &lcSortBy
		SELECT *, ;
			SUM(IIF(pmade > 0,((pbad/pmade)*100), 0000)) AS pctbad ;
			FROM temp2 ;
			INTO CURSOR temp3 ;
			ORDER BY &lcSortBy ;
			GROUP BY &lcSortBy
		SELECT *, ;
			SUM(pbad * purwght) AS lbsbad, ;
			SUM(pmade *purwght) AS lbsmde ;
			FROM temp3 ;
			INTO CURSOR csrScrap ;
			ORDER BY &lcSortBy ;
			GROUP BY &lcSortBy

VFP 6.0. I am rewriting a project from DOS FOX. The code I am trying to duplicate is as follows.

* a loop that sums the pbad1,pbad2.... to pbad30 to pbad
pmade = pgood + pbad && pgood field from table
pctbad = 0.00
IF pmade > 0
pctbad = (pbad/pmade) * 100
ENDIF
lbsmde = pmade * purwght
lbsbad = pbad * purwght

variables created do not need to be stored.

Thanks for any help.

Judi

 

[Judi201]

Mike,
Absolutely no offense taken. I know that is poor design but unless the company I am doing this for will see that other sites will accept my changes in structure I will be stuck with it. For the present I am working on two systems. One with several things like this and the other following rules for normalization. Actually I have to maintain the individual bad1,bad2... as they indicate a 'type of badness' and that is what I need a table for.
I am just trying to get into Visual (OOP) but had a strong background in Database way back when Foxpro was the hottest thing around. Been out of it for all of those years.

Thanks for your reponse. I understand it and it seem that is the best I can do for now. I truly appreciate your taking the time to respond.

Judi

 

[Judi201]

Mike,

The other thing is in VFP8 such a query will crash unless you SET ENGINEBEHAVIOR 70 - which I never recommend.

The more I thought about that, I knew that I had not pushed hard enough to get consent to normalize data (my first real project since retiring and getting back in this - little lack of confidence I suppose ) So I used that fact and a little research on my own and went back.

Result: I will not need to use the SELECT you help me with!!!

Thanks for taking the time to point that out to me.

Judi

 

[Judi201]

Yes, and now I feel much better about what I am doing. The fact that this would not run on later versions was the kicker. As I have said, they are still running on a DOS Fox application. The 'boss' is not a techie at all but he understands having to redo the program in a few years. Watching the bottom line is what he does know.

Thanks again.

Judi