Formula Parsing

Jan 26, 2006

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

View 3 Replies


ADVERTISEMENT

Transact SQL :: Parsing Formula Text To Float

Apr 27, 2015

There is a column in a table that has values like '23 + 45 + 63', '2 + 54 - 22'... and so on. I want to get the result of this formula as float like 131, 34... and so on. Is that possible with a SELECT statement. I tried:

SELECT CAST('23 + 45 + 63' AS FLOAT)

Didn't work.

View 14 Replies View Related

Reporting Services :: Add Formula To Formula Bar In Excel

Sep 1, 2015

In SQL reporting, How do I add the formula in the Formula bar?All the data is coming from a sproc.

View 3 Replies View Related

SUM(IF( Formula

Jan 18, 2008

Having a hard time writing a formula in RS...

Trying to say if the funding date is equal to today or before the beginning of the month then sum the loan amount.


Looking for something like this SUM(IF(Funding Date,>=Date(),Loan Amount))


Know it not right but someone please help!

View 1 Replies View Related

Formula In SQL

Jan 26, 2008



I am create a database and want to store some value automatically in some field i.e say i have 3 column 1) salary(int),2)tax(int) and 3rd field total salary(float) . i want to automatically fill total salary field as Column1-Column2 while i daont have any Idea How to do that?
please help me?
any idea or tutorial..example anything...

View 3 Replies View Related

Formula In Sql Server

Nov 17, 2007

hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application
thanx
 

View 1 Replies View Related

Using Formula For Colum Name!!

Oct 2, 2005

i wonder what is the best approach to use !!!i have creditLimit column in Customer Table the default value will be 500 and this limit to allow users to send sms from my website ..... every month they will be allowed to send 500 sms referring to the credit limit column.... now !!if the user sent today some sms and after few days sent another 20 and after one week he sent 150  SMS as a total so that means he has only 350 SMS as credit to use this month!!!so what is the best approach to implement this solution ? shall i have another field in the table called 'CreditUsed' and that will be updated each time the user will send SMS and this value will be compared with the credit limit ORi use the formula for the column to calculate the credit left and do the maths !!!what is the best approach as you think ???thnaks for reading this question !!

View 7 Replies View Related

Getting Values Into A Formula

Mar 30, 2006

hi there.
I have asked this question before in a different section of the forum without much reply, and its probably because i was asking the wrong people.
I need to calculate a qouta for an election.
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota =  (count(Vote) + 1) / ((11) + 1);
My problem is this: how to i get the count(vote) value from the Sql Statement to the formula.

View 2 Replies View Related

Formula Not Working

May 10, 2006

This does nothing:([rush24] + [rush6] + [addLocationsTotal] + 50)
This gives me my total correctly:([rush24] + [rush6] + 50)
All column are numeric(9) except the "total" column which is numeric(13).
Why is this happening?

View 1 Replies View Related

Using The Formula Proprity

Jun 13, 2002

I want to use the proprity Formula related to a column propreties :
Exempel : table XFRS i want to pupulate the field XF2 (int 4) using the value of another Field XF1 (int 4) Which is an identity field.
But When i try to insert into the table XFRS then i had an error
"Insert faild because the following set options have incorrect settings"
'ARITHABORT'"

Anyone to help me ? Thinks

View 1 Replies View Related

Formula Columns

Nov 3, 2004

Can anyone tell me how to find out if a column is a 'formula' or computed column - and what the formula is?

sp_columns @table_name = 'table' gives me most of the stuff I want but it doesn't show what the formula is for any formula columns...

Cheers in advance,

View 1 Replies View Related

Cast Formula

Apr 10, 2008

I have a quick question. created a report with this formula below. It works when I put a date range from 1-1-2007 to 2-1-2007, but when I put in a date range of 03-01-2008 through 03-31-2008. i get an error message of arithmetic overflow error converting numeric to data type numberic.

So instead of haveing the formula below be (4,2) I put it as (5,2) and now it work. Why is that?

CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings

View 3 Replies View Related

Cross Tab Formula

May 26, 2008

in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.

my table data consists like below:
country_name Revenue PERIOD_TYPE_OUT Amount_out
UK Solutions01 Apr 2007 To 31 Mar 200856.83000000
UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000

while iam using the cross tab in front end application data view is coming as
country_name

01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008
solution solution
uk 116.07 56.83


Actual o/p should be:

01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth
solution solution
uk 116.07 56.83 -0.51


can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate
is :
(56.83-116.07)/116.07
([current year]-[previous year])/[previous year]

View 2 Replies View Related

How To Put Formula In Sql Filed

Apr 18, 2007

i have two tables .Table 1 contains certain columns with values.Table 2 shud get those data from table1 into its column and display the calculated result. Any idea how do i go abt it,bcuz table2 shud contain a formula so as to calculate.

View 5 Replies View Related

Maths Formula's In SQL

Mar 8, 2008

Hi,

I want to store, mathematical formulas, in SQL, now how can i store integration signs, fractions, and various types of other symbols.

The output must be on html page using ASP. and also on VB frontend.

Pls. assist.

View 5 Replies View Related

SQL Formula Error

Mar 31, 2008

SELECT ID, CalibScoreAVGQA,
COUNT(CalibScoreAVGQA) AS Expr1
FROM dbo.TechPhonesCalibScoreAVGQA

Any idea why I'm getting "dbo.TechPhonesCalibScoreAVGQA.ID is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause

View 8 Replies View Related

Formula Problem

Feb 17, 2006

env: sql server 2000objective:add a formula for an INT column to the FORMULA field or the DEFAULTVALUE field in DESIGN VIEW.all of the following attempts failedIIF (columnName = 0, "1", columnName + 1)IIF (columnName = 0, 1, columnName + 1)IIF (0, "1", columnName + 1)caveat: cannot use IDENTITYREASON: I'd like have sets of repeatable values for this row, e.g.set a1 -- row 12 -- row 2345set b1 -- row 623set c1 -- row 9234Underlying rationale is to support OO design I understand probably it'sgoing to be huge headache for lots of people down the road but ...TIA.

View 3 Replies View Related

Formula Column.

Jul 20, 2005

HI,I have a problem in formula column.I have 8 1 bit varibles in a tablefor ex: Flag1, falg2, flag3 ...Flag8Now I want to create another variable as a small integer and copy allthe flgas to that field.For ex:(flag1 << 0x80) | (flag2 << 0x40) | ..... | flag8I tried all possible ways?Let me know how to write the formula for this column.Thanks,Venkat.

View 1 Replies View Related

Formula Column

Jul 20, 2005

Hi,I would like to create a calculated column using the formulasection for a table. I am having some trouble doing this.The table's name is ReportParameter. The calculated column's name istbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.I would like to use an If then statement such as the following (inpsuedo code):If tb1 = 1 then tbcalculatedcolumn = 1Elseif tb2 = 1 then tbcalculatedcolumn = 2EndifThanks for the help,Bill

View 6 Replies View Related

Formula In A Query

Oct 30, 2007

fellows
i am working in this formula


((4 * dbo.Drillability.WEIGHTONBIT)

/ (POWER(PD_Data.dbo.db_drill_hole_base.loaded_diameter, 2) * PI()) + (8 * dbo.Drillability.RPM * dbo.Drillability.TORQUE)

/ (POWER(PD_Data.dbo.db_drill_hole_base.loaded_diameter, 2) * dbo.Drillability.ROP)) * 0.006894757 AS SPEC_ENERGY_MPa


but my problem is that i have ceros in some records, i was thinking in to use ISNULL but when i tryed to run the query I got error close to 'as'........someone can help me whit this.
cheers
edwin

View 12 Replies View Related

Sql Script Formula

Apr 4, 2008



hy guys
i am trying to calculate a formula in sql, but
in the field lbs1 sometimes has ceros and the lbs2 sometimes has ceros as well

the resul of the formula is null, but this is supposed to be a value. the rest of fields always have data.

SQRT((((db_linear_density_1.[Linear density_1] * dbo.Assay_acquire.[lbs1(Kg)] + PD_Data.dbo.db_linear_density.[Linear density_2] * dbo.Assay_acquire.[lbs2(Kg)]) / (dbo.Assay_acquire.[lbs1(Kg)] + dbo.Assay_acquire.[lbs2(Kg)])) * ((dbo.HOLELOCATION.DEPTH - PD_Data.dbo.drillhole_base_MOR_MET_2004.stem) * 0.3048)) / (((dbo.Assay_acquire.[lbs1(Kg)] + dbo.Assay_acquire.[lbs2(Kg)]) / dbo.Assay_acquire.holetons) * (dbo.Assay_acquire.holetons / (dbo.HOLELOCATION.DEPTH * 0.3048 * dbo.Assay_acquire.[holearea(m2)]))) / 15) * 0.9325

cheers

View 3 Replies View Related

Reverse Formula

Feb 12, 2008



Hi . My database seems to be somehow encrypted by the software I used. When I try to export my data some fields are encrypted like this:


1 => 31911
2 => 63758
3 => 95540
4 => 127258
5 => 158912
6 => 190504
7 => 222032
8 => 253498

I'm really sure it's not some complicated formula (because I managed to recover other fields) . I just want to reverse the formula used to "encrypt" this data.
Thank you. Excuse my english.

View 1 Replies View Related

Excel Formula

Jan 7, 2008



I have two colums with two different date ranges







1/2/08 11:41 AM

1/4/08

12/27/07 9:38 AM

12/27/07

12/27/07 12:55 PM

1/2/08

1/4/08 11:20 AM

1/4/08


In the third column I'm trying to stated if the dates are different but when i change the first column to show just the date and not the time it still says they are different. How can i round or change the first column to just reflect the date and not the time so i can compare it to the second column.





























View 4 Replies View Related

Placing A Formula

May 19, 2008



Hi everyone, I have a small dilema here. I am running SSRS 2000, and I have a report that is grouped by Part Number. I have =Fields!Formula.Value < .1 so I can view only what is greater than .1 Well, my problem is that when I put it under the group Visibility properties, it does not pull all the data. and if i put it under detail Visibility. It does show me everything but any Part Number that is less than .1 shows just the part number but no detail. How can I make my report show all my data but without leaving data out????

Thanks ahead of time,

Abner

View 1 Replies View Related

Sum Formula That Always Adds To Zero

Sep 21, 2007

OK, I'm new to Excel 2007 but have used excel for a long time. I have a column of numbers and I'm trying to add them up. I click Sum, I highlight to cells I want to add and hit enter and it comes up with zero. The formula looks fine =SUM(G125:G127) and there are numbers in those cells (that don't add to zero) but it keeps adding them to zero. Can someone tell me why before I put my fist through my monitor?

When I go in and just click on once cell and then hit + and then another and + and another and + and then hit enter, I get #VALUE. So there must be something about how the numbers in the cells are formatted?? They look like numbers to me?
Thanks, Peter

View 2 Replies View Related

Need Help To Formula Sql Query For The Following

Apr 2, 2008

Table A Table B

101 Martha Steward Corp 101 Martha Steward

102 Gloria Gardens 101 John Pickle

103 Judi's Hockey Service 101 Ray Lettuce

104 Randy Bowling Ball 101 Mike Salad

105 Byron Ad Agency 102 Gloria Williams







102 Bob Jeans

103 Judi McClair

103 John Nolte

104 Randy Koolaid

104 Ike Smith

104 Blade McRay

105 Byron Exec

105 Jim Rayburn

SQL result list

CustID Customer Contact1 Contact2 Contact3

101 Martha Steward Corp Martha Steward John Pickle Ray Lettuce

102 Gloria Gardens Gloria Williams Bob Jeans null

103 Judi's Hockey Service Judi McClair John Nolte null

104 Randy Bowling Ball Randy Koolaid Ike Smith Blade McRay

105 Byron Ad Agency Byron Exec Jim Rayburn null


My knowledge level? create a scalar procedure to loop thru Table B using cursor three time to bring back one contact at a time. this just seem inefficient. can I make one pass thru the data.

View 3 Replies View Related

Formula Question

Apr 20, 2007

I have this crystal formula

How can this be converted to a reporting services Expression



if {ITEMCODE} in ["A","B"]

then "TypeA"

else if

{ITEMCODE} in ["C","D"]

then "TypeB"

else

"TypeC"



View 4 Replies View Related

Formula In Stored Procedure

Feb 28, 2007

Newbie problem: Working with SQL Server 2000 (web admin),  I have a table named OrderItems created with this query:
 
CREATE TABLE dbo.OrderItem
            (fkOrderID     int            NOT NULL,
            fkMenuItemID int            NOT NULL,
            ItemSize            nvarchar(50)  NOT NULL,
            ItemName            nvarchar(50)  NOT NULL,
            Quantity          int            NOT NULL,
            LineValue       money            NOT NULL)
 
I have created a second table that will contain summary data (total quantity for each item):
 
CREATE TABLE dbo.OrderItemTotal
            (fkMenuItemID            int            NOT NULL,
            ItemName            nvarchar(50)  NOT NULL,
            TotalQuantity  int            NOT NULL)
 
I want to draw the fkMenuItemID and ItemName directly from the OrderItem table and have the TotalQuantity column dynamically sum the total number of items for each fkMenuItemID so that as orders are added, the OrderItemTotal table is automatically updated. 
 
This sort of thing is easy in a spreadsheet using formulas, so I assume that there must be comparable methods in T-SQL.  From browsing the archives I get the sense that I may need a calculated formula within a stored procedure.  This is what I have so far, which does not contain a calculated formula for the TotalQuantity column.
 
CREATE PROCEDURE dbo.usp_InsertOrderItemTotal
                        @MenuItemID                       int,
                        @ItemName               varchar(50),
            AS
                        INSERT INTO OrderItemTotal
                        VALUES(@MenuItemID, @ItemName)
 
Am I on the right track with my thinking?  Where do I go from here?  Any help with this would be greatly appreciated.

View 4 Replies View Related

Need Help For A Simple Formula For An Sql Statement

Apr 6, 2007

I have a products database on my online store that records 2 columns for each product:DateAdded: the datetime the item was addedTotalSales: the total sales generated since the item was added to the siteI have already figured out how to calculate best sellers with this formula:TotalSales / TotalDays I am adding a computed column to my database, I need to calculate the followingTotalSales / ("this is where i need something to use today's date and calculate how many days it has been since it was added")Then if I can do that, I will automatically have a column that will give me a number ranking each of my items. 

View 2 Replies View Related

SQL: Giving A Column A Formula

Jun 17, 2008

I have 3 columns, all integers. [col1] [col2] [col3]
Is it possible to assing a formula to [col3] which always takes the sum of [col1] & [col2]?

View 3 Replies View Related

Formula In SQL Enterprise Manager

Dec 2, 2004

I have a table with 2 columns and I want to have a 3rd column that combines the 1st and 2nd column automatically and populates the 3rd column.

i know this can be done with the Formula option in the 3rd column but I dont know how to combine columns. One is of datatype int and the other is of datatype varchar.


Here are my column names etc.

name var
--------------------
step int
action varchar

thanks

View 1 Replies View Related

Formula For Calculated Column

Sep 19, 2005

Hi,I'm struggling to get a calculated column to work in sql, the fields to be calculated are:[AdRevenue_a]     money[Admissions_a]      int[DoorPrice_a]       smallmoney[DoorSplit_a]        moneyAnd the calculation I require is:(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a ))  *  100This is what I think it should be but it doesn't work...convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))Any suggestions??

View 6 Replies View Related

Concatenation Formula For Int Columns

May 29, 2006

Column A, Column B and Column C : All Integer.
I want a concatenation. For example A=111, B=222. C should be 111222 NOT 333. Is it possible? If it's possible, what is the formula?
Thanks in advance...

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved