I'm working with a DB design that seems to me to be rather complex.
This is a very slimmed down version of what I'm doing, but I believe it
is enough to get my question resolved.
Here is my layout.
These 4 tables are used to generate a questionaire.
Survey OrderID
========= ==========
SurveyID OrderID
OrderID QuestionGrpID
QGrp Questions
============= =============
QuestionGrpID QuestionID
QuestionID QuestionText
The following two tables are used to calculate a report that is sent to
the customer.
RawData
=========================
OrderID
QuestionID
Value is string but is Cast as decimal for numeric formulas
Metrics
==============================================
QuestionGroupID | ReportText | Formula | MetID
==============================================
2 | % Support Staff of Total | OP21/(OP21+OP22+OP23) | 1
The OP references are questionIDs
Now to calculate the result for the report we programatically parse the
formula creating a temp table (table name = Temp & orderID & _ &
QuestionID) with OrderID and OPxx as the field names. We create one
table for each question.
We then use dynamic SQL again to calculate the result using the above
formula
SELECT OP21/(OP21+OP22+OP23) FROM Temp5_21, Temp5_22, Temp5_23 WHERE
Temp5_21.OrderID = Temp5_22.orderID AND Temp5_22.OrderID =
Temp5_23.OrderID
This select is used to create a single table of calculated values.
This table is in turn used to tell the customer how they compare to
other customers. Percentile, Mean, Median, Std Dev, and a few others. I
don't claim this part of the project, but I'm not sure how I might have
done it, had it been assigned to me.
MY PROBLEM!!!
Sometimes a 0 is valid data and is the denominator of a devision
calculation. Since this is so dynamic and it might be difficult to
determine when division is used. I need a way to default divide by 0
execptions to NULL. This DB is on a hosted server.
Thanks for bearing with me,
Greg Kelley