Dynamic formulas driven by table

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"yzarc" <yzarcman@.gmail.com> wrote in message
news:1105629819.876049.179950@.c13g2000cwb.googlegr oups.com...
> 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

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+OP22+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|||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

Dynamic Column Names

Hi guys 'n gals,

I am trying to achieve something a bit complex (or at least appears to be for me).

I have the following table structure:

UID, GroupID, ColumnName, ColumnValue

here is some example data:

UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven

and here is what I would like the example to come out like:

GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven

It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...

I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:

Code Snippet

DECLARE @.ColumnName varchar(128)
DECLARE @.Sql varchar(255)
SELECT @.ColumnName = [ColumnName] FROM tblSourceData
SELECT @.Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@.ColumnName+'] FROM tblSourceData'
EXEC (@.Sql)

Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?

Regards,
Justin

You need not to have dynamic SQL, you are trying to achieve the table pivoting..

Code Snippet

Create Table #data (

[UID] Varchar(100) ,

[GroupId] Varchar(100) ,

[ColumnName] Varchar(100) ,

[ColumnValue] Varchar(100)

);

Insert Into #data Values('1','1','MAC Address','8a7sd87sad');

Insert Into #data Values('2','1','IP Address','192.168.1.100');

Insert Into #data Values('3','1','Name','John');

Insert Into #data Values('4','2','MAC Address','09a8sd098as');

Insert Into #data Values('5','2','Name','Steven');

On SQL server 2000/2005,

Code Snippet

Select

GroupId,

Max(Case When[ColumnName] = 'MAC Address' Then ColumnValue End) as [MAC Address],

Max(Case When[ColumnName] = 'IP Address' Then ColumnValue End) as [IP Address],

Max(Case When[ColumnName] = 'Name' Then ColumnValue End) as [Name]

from

#data

Group By

GroupId

Only on SQL server 2005,

Code Snippet

Select * From

(Select GroupId,ColumnName,ColumnValue from #data) as Data

Pivot

(

Max(ColumnValue) For ColumnName in([MAC Address], [IP Address], [Name])

)

as PVT

|||

The one problem though is that you are still manually specifying column names, when I don't want to have to manually change my SQL each time I add a new column in the table... Is there no way of making this more dynamic?

Regards,

Justin

|||

Using the previous temp table data,

On SQL Server 2000/2005

Code Snippet

Declare @.PreparedQuery as Varchar(8000);

Declare @.SqlQuery as Varchar(8000);

Set @.PreparedQuery = 'Max(Case When[ColumnName] = ''?'' Then ColumnValue End) as [?]'

Set @.SqlQuery = 'Select GroupId'

Select @.SqlQuery = @.SqlQuery + ',' +Replace(@.PreparedQuery,'?',ColumnName)

From

(select Distinct ColumnName from #data) as Data

Exec (@.SqlQuery + 'from #data Group By GroupId')

On SQL Server 2005

Code Snippet

Declare @.PreparedQuery as Varchar(8000);

Declare @.SqlQuery as Varchar(8000);

Set @.PreparedQuery = '[?]'

Set @.SqlQuery =

'Select * From (Select GroupId,ColumnName,ColumnValue from #data) as Data

Pivot (Max(ColumnValue) For ColumnName in('

Select @.SqlQuery = @.SqlQuery + Replace(@.PreparedQuery,'?',ColumnName) + ','

From

(select Distinct ColumnName from #data) as Data

Set@.SqlQuery = Substring(@.SqlQuery,1,Len(@.SqlQuery)-1) + ')) as Pvt'

Exec (@.SqlQuery)

|||Works a charm, and is a lot less code than I originally thought it would be, thank you!