Help with numeric calcs in sp_
-
I admit it; I'm hpelessly confused and I hope some one can help.
I've read many books, articles and so forth trying to understand how I can use stored proceedures to produce calculated results. I have had some success with simple add, subtract, multiply operations but also run into problems when I try to calculate a percentage which is, of course, not an integer. The value is always shown as zero.
The second problem I have is one of division by zero. I can't know whether my divisor will be zero until the records are SELECTed and, anyway, I need to handle those cases conditionally which implies an if..else construct.
Here are 2 simple tables (SQL2000):
W
-----
ID
CTC (cost to complete)
CTD (cost to date)
CP (contract price)
BTD (Billed To Date)
JobID (FK)
Another Table:
J
-------
JobID (PK)
ContractPrice
OrigEstCost
The four numbers in table w are used in 12 different calculations several of which need to be decimal types. Number of rows to be selected and processed is indeterminate - could be 1 or could be ,000's
Here is a simplified form of my Select statement:
Select Distinct Jobs.JobID, w.detailid,
PctComp=(w.ctd)/(w.ctc+w.ctd),
GP=(w.CP-w.ctc-w.ctd),
GPP=((w.CP-w.ctc-w.ctd)/w.ctc+w.ctd),
EGP=GP*GPP,
w.BTD as BTD
WHERE ......
GPP needs to be cast as a decimal number. And since the same calculation of GP is used in EGP I need to solve that probelm as well. The most logical way to do that would seem to be to have the non-table items (GP, GPP, EGP etc) as variables local to the stored proceedure and to use those values in subsequent calculations. I've tried every syntax and example I can think of and cannot get that to happen.
Also, w.ctd+w.ctc may be equal to zero in some cases. I would like to process that possibility conditionally but, to be honest, I haven't a clue how to begin that process.
I would be extremely grateful for any help, suggestions, link examples anyone could provide. Your assistance will be instructive well beyond the solution to this specific question.
Thanks in advance.
Peter
-
Hello Peter,
Couple suggestions that may help. One is to take a look at user defined functions which are available in SQL2K - I think you'll find they provide code reuse plus make it a lot easier to visualize whats going on. You can easily employ if/then testing there to handle your zero cases. The other is that to get the right data type result, I think you need to cast all the variables used to derive the result as that data type as well - in other words, if you divide two integers you get an integer, casting it after the calculation won't help - you've lost the decimal portion already!
Now if we can wake Steve Jones or Jon Winer from their respective hibernative states, we might get you some sample code!
Andy
-
Andy,
Thanks for your suggestions, appreciate it. I will begin working with both today and hope for a SQL epiphany (or a sample from Jon or Steve
)Peter
-
OK, OK, I'm awake. BTW, I'm not that lazy, I just live in Denver.
OK, I think that a UDf would be great for calculations. they really make things easier to read.
A couple of suggestions.
1. Divide by 0.
Try something like this:
Select Distinct
Jobs.JobID
, w.detailid
, case when (w.ctc + w.ctd) = 0
then 0
else (w.ctd)/(w.ctc+w.ctd)
end 'PctComp'
,GP=(w.CP-w.ctc-w.ctd)
,case when (w.ctc+w.ctd)=0
then 0
else ((w.CP-w.ctc-w.ctd)/w.ctc+w.ctd)
end 'GPP'
, GP*GPP 'EGP'
, w.BTD 'BTD'
WHERE
(sorry about reformatting. my habit)
As far as the decimal issues, you need to be sure that all items are of similar types. If some values are integers, you may get an implicit conversion.
If you'd like, send me the DDL and some sample data (BCP out) along with what you're trying to do. (steve@dkranch.net). I'll send some suggestions and write an article out of it.
Steve
Steve Jones
-
Hi Steve,
Oh, Denver... now I understand!
Thanks very much for your suggestions. I made out pretty well following your advice - the case construct worked well. Not one to follow the 'if it ain't broke don't fix it' adage I wonder if there is an If..else method for accomplishing the same goal. More than idle curiosity really I am trying to get my arms around how it all works.
There is still a problem with using the alias names GP and GPP in the example as variables. Not a big deal; just means that I have to repeat the calculation of each of them instead of using the calculated variable. I'm guessing that GP and GPP are not actually variables but are simply column names - that sound right to you? Again it isn't a problem for the moment and perhaps using UDF's will obviate the need to deal with it at all. UDF's in place of the GP and GPP calculations are next on my list but I did want to let you know that your suggestions helped a lot.
I think an article on the subject would be terrific and very useful to many of us. Be glad to provide the example data; just tell me how I get it to you. (DDL/BCP ??)
Appreciatively,
Peter
)Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply