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):
CTC (cost to complete)
CTD (cost to date)
CP (contract price)
BTD (Billed To Date)
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,
w.BTD as BTD
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.