Convert Excel "If" function to T-SQL query

Question

I have a table in a SQL Server database and Excel that looks like this

Using Excel to calculate the value of "D" in column "H" I have used the following formula:

=IF(G2="NULL",100,IF(A2="NULL",((E2-3-F2)/D2),IF(D2="NULL",((B2-3-C2)/A2),IF(((B2-3-C2)/A2)<((E2-3-F2)/D2),((B2-3-C2)/A2),((E2-3-F2)/D2)))))

The formula works fine and if I want to change the value 3 to 3.2 in the formula and update the values in Excel, I just change 3 to 3.2 to make it look like the formula below:

=IF(G2="NULL",100,IF(A2="NULL",((E2-3.2-F2)/D2),IF(D2="NULL",((B2-3.2-C2)/A2),IF(((B2-3.2-C2)/A2)<((E2-3.2-F2)/D2),((B2-3.2-C2)/A2),((E2-3.2-F2)/D2)))))

However I don't know how to convert this Excel formula to a SQL query and I would appreciate if anyone can help me in writing the correct SQL query statement based on the above formula. Thanks in advance.

Which DBMS are you using? mysql <> sql server. And what have you tried? — Sean Lange
– Sean Lange, Commented Nov 19, 2015 at 15:50
Thanks for your reply Sean, I am using MS SQL Server 2008 and I have not tried anything. — Hab G.
– Hab G., Commented Nov 19, 2015 at 16:04

tezzo · Accepted Answer · 2015-11-19 16:07:25Z

2

In SQL SERVER you can use CASE expression to evaluates a list of conditions and returns one of multiple possible result expressions.

SELECT
CASE WHEN G2 IS NULL THEN 100 
    ELSE 
    CASE WHEN A2 IS NULL THEN (E2-3.2-F2)/D2 
        ELSE 
        CASE WHEN D2 IS NULL THEN (B2-3.2-C2)/A2
            ELSE
            CASE WHEN (B2-3.2-C2)/A2 < (E2-3.2-F2)/D2 THEN (B2-3.2-C2)/A2
                ELSE (E2-3.2-F2)/D2 
            END 
        END 
    END 
END AS Test
FROM YourTable

answered Nov 19, 2015 at 16:07

tezzo

11.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hab G. Over a year ago

Thanks tezzo, this really helped me.

Collectives™ on Stack Overflow

Convert Excel "If" function to T-SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related