Dynamic formulas driven by table

[yzarc]

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.OrderI D = Temp5_22.orderI D AND Temp5_22.OrderI D =
Temp5_23.OrderI D

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

[Simon Hayes]

Re: Dynamic formulas driven by table


"yzarc" <yzarcman@gmail .com> wrote in message
news:1105629819 .876049.179950@ c13g2000cwb.goo glegroups.com.. .[color=blue]
> 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.OrderI D = Temp5_22.orderI D AND Temp5_22.OrderI D =
> Temp5_23.OrderI D
>
> 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
>[/color]

Check out SET ANSI_WARNINGS, SET ARITHABORT and "Behavior if Both ARITHABORT
and ARITHIGNORE Are Set ON" in Books Online - this will do what you want.
But, it's not a recommended solution, because it means you can't use
features like distributed queries and indexed views, and it may create
problems with other code.

Alternatively, you might be able to store your formulae with a NULLIF around
the divisor:

OP21/NULLIF((OP21+OP 22+OP23), 0)

If that's not possible, and you can't be sure what the divisor will be, then
you would probably have to look at solving it outside the database, either
by parsing the formulae to insert a NULLIF dynamically, or perhaps by doing
some calculations externally.

Simon

[yzarc]

Re: Dynamic formulas driven by table

Thanks,
I appreciate the reply. I'm using a fairly basic parser to divide the
OP codes out and creat the table. I may look at tagging the OP codes so
that I can strip anything out that is not an OP code for creating my
tables.

Thanks again,
Greg