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