Posted by Mike on 01/26/06 18:06

Hi,

I have three tables in the following structure (simplified):

Table 1: Containing the customers
-------------------------------------------------
create table Customers
(
[cusID] int identity(1, 1) not null,
[cusName] varchar(25) not null
)

Table 2: Containing the customer data fields
---------------------------------------------------------------
create table Data
(
[datID] int identity(1, 1) not null,
[datName] varchar(25) not null,
[datFormula] varchar(1500)
)

Table 3: Containing the customer data values
-----------------------------------------------------------------
create table Values
(
[cusID] int not null,
[datID] int not null,
[valValue] sql_variant
)

In this structure the user can add as many data fields to a customer as
he wants (e.g. Country, City, Email, Phone, ...). I have added triggers
which create a view similar to a pivot (I am working in SQL 2000) and
add triggers to the view so it is insertable, deletable and updateable.

What I would like to do, is allow the user to create new fields where
the values are based upon a calculation. This calculation would be done
through a formula similar to what he would do e.g. in excel (this
formula is stored in the dimFormula field then).

An example might help. Let's assume the user created a field 'Sales'
(containing last year's sales) and 'Invoices' (containing the number of
invoices that were created for him last year). Now, he wants to create
a field 'AvgSales' with the formula '[Sales]/[Invoices]'.

(Note that through adding these data fields, the above view was created
(let's assume it is called vw_Customers and contains the columns [ID],
[Name], [Sales], [Invoices], [AvgSales]).

What I am looking for is a function which can parse this formula into a
t_sql query which runs the calculation. So, the formula
'[Sales]/[Invoices]' would be translated into (let's assume there are
no records with NULL or zero invoices):

update vw_Customers
set [AvgSales] = [Sales]/[Invoices]
from vw_Customers

I am able to do the above with simple calculations (where you can even
use sql functions e.g. year, len, ...). Now I would like to take this
one step forward into the possibility of using functions with more
variables.

For example. Let's assume, the user wants to add a rating (field called
'Rating') to his customers based upon the result of 'AvgSales. He
enters the formula 'if([AvgSales] > 2500, 'A', 'B')'.

If anyone could help me on this, I would be very grateful. Thanks.

M

[Back to original message]