473,881 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Jan 26 '06 #1
3 5108

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.

Jan 26 '06 #2
Mike (mi************ *@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 26 '06 #3
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.

Jan 27 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1873
by: Al Christians | last post by:
I've got an idea for an application, and I wonder how much of what it takes to create it is available in open source python components. My plan is this -- I want to do a simple spreadsheet-like application: I've used a wxGrid, so I expect that wxPython will do fine for the user interface. The spreadsheet can be organized vertically, one item per line. It might need no more than 100 rows or so. On each line, the user will enter in one...
8
6942
by: VM | last post by:
I'm trying to find out what the best way would be to parse a formula so I can then calculate it with its real values. For example, a user may enter ((sys_empHours*db_empSal)/db_totalHours) and I would need to parse all of that so that I can get the tokens that need to be replaced with data (sys_empHours, db_empSal, etc...) and then plug them back in. Thanks.
0
1823
by: RJN | last post by:
Hi I have a main report and a sub report. I have a formula field on the main report and one on the sub report. I want the formula in the subreport to be evaluated after the formula in the main report. The sub report is called in the report header of the main report and the formula fields are in the details section of the reports. Main report looks something like this.
0
1626
by: RJN | last post by:
Hi Sorry for posting this message again. I have a main report and a sub report. I have a formula field on the main report and one on the sub report. I want the formula in the subreport to be evaluated after the formula in the main report. The sub report is called in the report header of the main report and the formula fields are in the details section of the reports. Main report looks something like this.
0
2208
by: rjn | last post by:
Hi I have a main report in which I have inserted a sub report. I have a formula field on the main report and one on the sub report. I want the formula in the subreport to be evaluated after the formula in the main report. The sub report is called in the report header of the main report and the formula fields are in the details section of the reports. Main report looks something like this. Report Header
17
2798
by: Mark | last post by:
I must create a routine that finds tokens in small, arbitrary VB code snippets. For example, it might have to find all occurrences of {Formula} I was thinking that using regular expressions might be a neat way to solve this, but I am new to them. Can anyone give me a hint here? The catch is, it must only find tokens that are not quoted and not commented; examples follow
1
5073
by: barnzee | last post by:
Hi all, newbie here, but having a go I am trying to build a stock watchlist in excel 2007 with a dynamic link to a DDE server (paid for from a broker).There is no add-in or plug-in, I just CTL ALT & drag each code from a watchlist in the program I am using and place it in a cell in excel, however can only choose one data field at a time. There are 14 data fields and over 150 codes in my list which makes 2100 cells. (My guess is about 3 days...
1
922
by: Tom C | last post by:
Assume a user supplied excel cell formula of "=Model!E75" which could change so I don't want to hard code it. Inside of a loop I need to make it increment the row to refrence E76, E77, etc. Any suggestions on a quick easy way to replace the 75 with the next value? I know I could write a few lines of code but just wondering if anyone has a slick one liner or so... Thanks!
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11103
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10720
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10403
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7113
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4196
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3225
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.