Convert Formula from Excel to SQL Server Format

Hi guys, i need help to convert excel formula to sql server syntax.

Here's the formula

=IF(
([@[Net Available Stock Quantity]]+[@Subcon]+[@[In Progress WIP]]+
IFERROR([@[Raw Material In Store]]/[@[Demand Material]],0)+
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]],0)+
[@[Outstanding WIP]])>[@[Maximun Stock]],
([@[Net Available Stock Quantity]]+[@Subcon]+[@[In Progress WIP]]+
IFERROR([@[Raw Material In Store]]/[@[Demand Material]],0)+
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]],0)+
[@[Outstanding WIP]]-[@[Maximun Stock]]),
IF(([@[Net Available Stock Quantity]]+[@Subcon]+[@[In Progress WIP]]+
IFERROR([@[Raw Material In Store]]/[@[Demand Material]],0)+
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]],0)+[@[Outstanding WIP]])<[@[Minimum Stock]],
-[@[Minimum Stock]]+([@[Net Available Stock Quantity]]+[@Subcon]+[@[In Progress WIP]]+
IFERROR([@[Raw Material In Store]]/[@[Demand Material]],0)+
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]],0)+[@[Outstanding WIP]]),0)
)

Here's what i've done :

SELECT [Item No] CASE WHEN (
[Net Available Stock Quantity]+Subcon+[In Progress WIP]+CASE (
WHEN ([Raw Material In Store] AND [Demand Material] != 0 THEN [Raw Material In Store]/[Demand Material] ELSE 0) +
WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw Material]/[Demand Material] ELSE 0) +
[In Progress WIP]) > [Maximum Stock])
THEN (
[Net Available Stock Quantity]+Subcon+[In Progress WIP]+CASE (
WHEN ([Raw Material In Store] AND [Demand Material] != 0 THEN [Raw Material In Store]/[Demand Material] ELSE 0) +
WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw Material]/[Demand Material] ELSE 0) +
[In Progress WIP]) - [Maximum Stock])
ELSE (
[Net Available Stock Quantity]+Subcon+[In Progress WIP]+CASE (
WHEN ([Raw Material In Store] AND [Demand Material] != 0 THEN [Raw Material In Store]/[Demand Material] ELSE 0) +
WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw Material]/[Demand Material] ELSE 0) +
[In Progress WIP] < [Minimum Stock])
THEN (
[Minimum Stock]+[Net Available Stock Quantity]+Subcon+[Outstanding WIP]+CASE (
WHEN ([Raw Material In Store] AND [Demand Material] != 0 THEN [Raw Material In Store]/[Demand Material] ELSE 0) +
WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw material]/[Demand Material] ELSE 0) +
[Outstanding WIP])
)
END AS [Final Stock Over Max]

I don't know where and what is wrong. Tried paste to sql query, certain part higlighted as Incorrect syntax. Google syntax of SELECT CASE WHEN, i think it's all good.

Please help me

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
I am going to point out some of the issues that i see

1- SELECT [Item No] CASE WHEN
Answer: You can't do that it must be SELECT [Item No] = CASE WHEN

2 - I see that the case is wrong check the brakets
The braket in SELECT [Item No] CASE WHEN (
belong to line number 5 ,
[In Progress WIP]) > [Maximum Stock])
Answer: What that is wrong

3 - Line 2 i see CASE (
Answer: Wrong

4 - I see ELSE 0) +
Answer: its wrong

SOLUTION
Delete ALL case statement
add them line by line and run them one by one

Good
luck