428,829 Members | 1,828 Online
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

# Dynamic formulas driven by table

 P: n/a 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 Jul 23 '05 #1