Formula Translate from Excel to SQL 1

[soutener]

=IF(E4<0.95,(((0.95-E4)*H4)+G4),G4)


i'm working on a report that is semi done in excel, i want to make the report available in an ASP web page and i dont know how to make the forumula above work in SQL
is it possible? will i be forced to use t-sql (never used before)

thanks!

 

[rac2]

Assuming the database has a table with columns corresponding to the spreadsheet columns E, G and H this is straightforward. In other words, the spreadsheet has an area which is a table with column headings and many rows, each row with the same kinds of data about things of the same kind.

A formula in a spreadsheet such as you have posted applies to a single cell and may take data from anywhere, even from rows above and below the cell. This is not possible in a SQL query. A SQL query must obtain all values from the same row, the result of the calculation appears on the same row like another column.

Given that

SELECT colE, colG, colH,
            (
             CASE
                WHEN colE < 0.95 THEN (  ( (0.95 - colE ) * colH ) + colG  )
                ELSE colG
             END
            ) AS "aCalculation"
FROM MyTable

So the SQL equivalent of IF is CASE.

I included the individual column values in the SELECT list simply for reference, they are not necessary. For my example the database table is named MyTable and it has at least three columns named colE, colG, and colH. Your actual table will have meaningful column names such as the column headings in your spreadsheet table.

This query will yield the calculation for every row in the table. You may wish to add a WHERE clause to limit the results to certain rows meeting some condition such as a date range.

 

[soutener]

ColG is VendorPerc

Msg 195, Level 15, State 10, Line 1
'VendorPerc' is not a recognized built-in function name.


this error is displayed when i try it, any idea why?

SELECT AVGPERCSALE, Factor, VendorPerc
	(
	 CASE
		WHEN AVGPERCSALE < 0.95 THEN (((0.95-AVGPERCSALE)*Factor)+VendorPerc)
		ELSE NOFEAR.dbo.VendorPerc
	 END
	) AS "aCalculation"
FROM NOFEAR.dbo.ConsignTblMaster

 

[bborissov]

Because you missed the comma after VendorPerc, also there is no need to put this in brackets.

SELECT AVGPERCSALE, Factor, VendorPerc,
       CASE WHEN AVGPERCSALE < 0.95 THEN
           (((0.95-AVGPERCSALE)*Factor)+VendorPerc)
        ELSE
            NOFEAR.dbo.VendorPerc
        END AS aCalculation
FROM NOFEAR.dbo.ConsignTblMaster

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[soutener]

thanks bborissov!

nub error i guess.....
one more question regarding this..
how can i use that to update a column?
i tried :

UPDATE NOFEAR.dbo.ConsignTblMaster
SET VendorCut
       CASE WHEN AVGPERCSALE < 0.95 THEN
           (((0.95-AVGPERCSALE)*Factor)+VendorPerc)
        ELSE
            NOFEAR.dbo.VendorPerc
        END

but no dice

errors:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '0.95'.

 

[rac2]

SET column_name = some_expression

 

[soutener]

LOL!!!!!!

i'm such a nub

thanks

 

[munavary]

Hi Rac2

I am facing the same issue as soutener.But in my case since the formula keeps changes very often i want to save this forumlas in table and dynamically generate the SQL script for the formula.

In soutener's case you have hardcoded his formula but i want to generate the scipt for that formula dynamically and pass the values to do the calculations.

Kindly Advise.

 

[rac2]

The facility for dynamic SQL is the EXECUTE(' a string which would' + ' be a SQL statement') function.

That would be in a stored procedure. A stored procedure may have many T-SQL statements with IF THEN logic.

So you might call the stored procedure passing some parameters to it such as @discount, @use_formula, etc.

Then query the table of formulas to get the formula to use.

Then build the string representing the particular formula you wish to use today.

Then EXECUTE() it.

EXECUTE('some string') is like the eval("some string") in Javascript.

Note that the CASE expression is only for a SQL statement, whereas IF THEN is a type of statement in Transact SQL.

When the alternative formulas are knowable in advance, it is sometimes possible simply to write a more complicated query that handles all of the alternatives. This will probably be more efficient than to build a string and execute it.