Posted by ZeldorBlat on 01/26/06 22:31

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.

[Back to original message]