Formula Parsing

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 ar
e
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

Mike  wrote:
> 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

The best advice I can give you is to not try doing this with pure SQL.
You'll save yourself a lot of headache if you take some data that's a
little more "raw" and manipulate it in some other programming language
to get the desired result.

Mike (michael.matthys@hotmail.com)  writes:
> 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).
>...
> 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')'.

I can only echo "ZeldorBlat" don't do this in SQL. If you had been on
SQL 2005, you could possibly have used CLR modules for the task.

But I wonder if you are not barking up the wrong tree entirely. Have
you looked at Analysis Services? I'm completely ignorant about Analysis
Services myself, but I would not be surprised if it has some support
for what you are trying to do.

If you are dead set on doing this in SQL 2000, you have to choices:
1) require that the user uses T-SQL syntax, for instance
CASE WHEN [AvgSales] THEN 'A' ELSE 'B' END
2) Define you own forumla language, and parse it in client code and
define the columns in the views as the users defines his formulas.

Beside AS, you could also investigate what 3rd party products out
there that may address your needs.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Look up the EAV design flaw you have re-discovered and stop writing SQL
like this.  SQL is not a computational language; it is a database
language.