Excel Formulas - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Excel Formulas

Author

Topic

jtstruck
Starting Member

6 Posts

Posted - 2006-01-02 : 12:13:48

I've been given a project to convert an Excel spredsheet that someone put together and convert it to a SQL process so it can handle more data and run quicker. A majority of the spreadsheet is checking conditions of other cells on the spreadsheet. And if they meet certain criteria then perform a calcualtion, or take the MAX of two cells. It's all done with 'IIF' statements. I would like some suggestions on the best way to approach this...Thanks

Kristen
Test

22859 Posts

Posted - 2006-01-02 : 12:57:08

Hi jtstruck, Welcome to SQL Team!

The sort of spreadsheet you are referring to, and SQL Server, are a bit like chalk and cheese!

You could put all the "raw" data into a table and the progressively "massage" it with UPDATE statements until everything is as-you-want it.

Or you could write an Insert/Update TRIGGER on the table so that as a row is added the various calculations to that row are made (and stored in additional columns that are, in effect, for calculated values only). This won't be any use if some of the formula are based on other columns (like a running total, which is set to Zero when, say, "Column-A changes".

Or alternatively just store the raw data in a table and create a VIEW with all the calculated columns - so when you query the view the "other calculations" are performed real-time and their values available to you.

Kristen

jtstruck
Starting Member

6 Posts

Posted - 2006-01-02 : 15:02:50

What is the best way to put all the 'IIF' statements into a view? And would I be able to base other fields off of previous calculated fields.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-02 : 23:50:57

SQL Server doesnt support IIF. But you can use CASE

IIF(condition,value1,value2) is equivalent to

CASE When Condition THEN value1 ELSE value2 END

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 08:02:57

"What is the best way to put all the 'IIF' statements into a view?"

CASE statements as Madhi suggests.

CREATE VIEW MyView
AS
SELECT MyCostPrice,
MyTax,
MyCostPrice + MyTax AS MyBuyingTotal,
-- = MyCostPrice * IIF(MyCustomer = 'FRED', 1.5, IIF(MyCustomer = 'BILL', 1.6, 2.0))
MyCostPrice *
CASE WHEN MyCustomer = 'FRED' THEN 1.5
WHEN MyCustomer = 'BILL' THEN 1.6
ELSE 2.0
END AS MySellingPrice
FROM MyTable

"And would I be able to base other fields off of previous calculated fields."

Nope

, but you would if you used the UPDATE method - so you could do:


DECLARE @MyMarkup FLOAT

UPDATE U
SET    @MyMarkup = 
           CASE WHEN MyCustomer = 'FRED' THEN 1.5
                WHEN MyCustomer = 'BILL' THEN 1.6
                ELSE 2.0
                END,
      MyMarkupColumn = @MyMarkup,
      MyPriceColumn = MyCostPrice * @MyMarkup
FROM MyTable AS U
[/code]
Kristen

jtstruck
Starting Member

6 Posts

Posted - 2006-01-03 : 08:42:23

The CASEs and UPDATES is what I've been doing, I was hoping there might be a better way. Whoever created this Excel sheet got really carried away. Here is the latest IF statement that I'm trying to replicate. The cells indicated in the statement are already fields in my table...

=IF($E$4=0,0,IF($L$5=0,0,IF($H$2=0,0,IF($L$3>0,0,IF($L$3=0,0,MIN(AI13,MAX(AF13,AM13/(1-(AC13-L13)/AC13))))))))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 08:55:17

CASE When E4col=0 then 0 when L5col=0 then 0 when H2col=0 then 0 When....... end

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 08:56:54

Well ... boring as this may sound ... if it were me I'd reverse-engineer the whole thing to "English", and then write the SQL from there. SQL is not like Excel formulae, and doing the reverse-engineering step will bring to light a bunch of things that the XLS does that could be better-done differently, given how SQL works as compared to Excel.

Sorry about that ...

Kristen

jtstruck
Starting Member

6 Posts

Posted - 2006-01-03 : 10:44:10

No offence Kristen, but I'm going to try Madhivanan suggestion first. However I'm still unsure how to approach the MIN/MAX
section of the IF statement in my previous post. Any suggestions?

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 11:01:52

"No offence Kristen"

No problem, I'd start off that way too give this specific problem, but I would be gambling on not having to retrench and do it the boring way, and 9-times-out-of-10 I'd lose my shirt!

"I'm still unsure how to approach the MIN/MAX section of the IF statement in my previous post"

Split those columns out into a JOINed "child" table?

Damn, the shirt is not looking so good already!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources