Converting Excel Formula to SQL Syntax Calculation

Question

i need help solving my problem converting Excel Formula to SQL.

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)
)

And here's what i've done :

USE myDB

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) +
            [Outstanding WIP]) > [Maximum Stock])
        THEN ([Net Available Stock]+Subcon+[Outstanding WIP]+CASE (
            WHEN ([Raw Material] AND [Demand Material] != 0 THEN [Raw Material]/[Demand Material] ELSE 0) +
            WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw Material]/[Demand Material] ELSE 0) +
            [Outstanding WIP) - [Maximum Stock])
        ELSE (
            CASE
            WHEN([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] < [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],
FROM myTable

So, the problems when i paste to SQL Server Query,

On [Item No] Invalid Column Name. (Plenty error like this on certain name)
On second WHEN Invalid Syntax.
On CASE inside ELSE Invalid Syntax.
On AS Invalid Syntax.

I use THIS LINK as guide to convert the excel formula.

What do you guys think i'm doing wrong? It's been 2 days i try to convert, plenty example i've tried, but still the error appear.

UPDATED:

myTable structure:

[Item No]         nchar(24) 
Description       nchar(60) 
UOM               nchar(10) 
Stock             int   
Sales             int   
[Minimum Stock]   int   
[Maximum Stock]   int   
[Subcon Code]     char(24)  
[Raw Material]    char(24)  
[Description Material]  nchar(60)   
[Demand Material]   char(10)    
[In Progress WIP]   char(10)    
Subcon            int   
[Net Available Stock Quantity]  int 
[Stock Over Maximum]            int 
[Raw Material Outstanding]      int 
[Raw Material In Store]         int
[Outstanding WIP]               int

I can't debug this now because I'm not in front of SSMS, but what I would suggest is to get the smaller pieces of the API working, and then build up the expression. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 6, 2018 at 2:31
You're trying to use Excel syntax in SQL Server. Excel (and Access) are not SQL Server. They use totally different syntax. You're going to need to learn TSQL first, and then do things in smaller pieces to get it figured out rather than trying to do it all in one pass. — Ken White
– Ken White, Commented Apr 6, 2018 at 2:32
Please show the definition of myTable - all fields and data types. Thanks. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Apr 6, 2018 at 2:34
@KenWhite I have done 2 column in smaller piece of calculation. It goes okay, no problem at all. This formula is the last piece of my table — S. Ideal
– S. Ideal, Commented Apr 6, 2018 at 2:37
That's a very large last piece, and the problems you started with (your point #1) should have been issues you ran into long before now. And when it gets to SQL Server, it's no longer a formula. It's a SQL SELECT statement. The difference is important. — Ken White
– Ken White, Commented Apr 6, 2018 at 2:38

RoMEoMusTDiE · Accepted Answer · 2018-04-06 03:46:24Z

1

try this code and feel free to reorder them...

    CASE
    WHEN ([Net Available Stock Quantity] + [Subcon] + [In Progress WIP] +
      COALESCE([Raw Material In Store] / NULLIF([Demand Material], 0), 0) +
      COALESCE([Outstanding Raw Material] / NULLIF([Demand Material], 0), 0) +
      [Outstanding WIP]) > [Maximun Stock] THEN CASE
        WHEN
          ([Net Available Stock Quantity] + [Subcon] + [In Progress WIP] +
          COALESCE([Raw Material In Store] / NULLIF([Demand Material], 0), 0) +
          COALESCE([Outstanding Raw Material] / NULLIF([Demand Material], 0), 0) + [Outstanding WIP]) < [Minimum Stock] THEN [Minimum Stock] + ([Net Available Stock Quantity] + [Subcon] + [In Progress WIP] +
          COALESCE([Raw Material In Store] / NULLIF([Demand Material], 0), 0) +
          COALESCE([Outstanding Raw Material] / NULLIF([Demand Material], 0), 0) + +[Outstanding WIP])
        ELSE ([Net Available Stock Quantity] + [Subcon] + [In Progress WIP] +
          COALESCE([Raw Material In Store] / NULLIF([Demand Material], 0), 0) +
          COALESCE([Outstanding Raw Material] / NULLIF([Demand Material], 0), 0) +
          [Outstanding WIP] - [Maximun Stock])

      END
    ELSE 0
  END

edited Apr 6, 2018 at 3:46

answered Apr 6, 2018 at 3:39

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

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

1 Comment

S. Ideal Over a year ago

I once read about COALESCE but pass it out due to not understand it very well. BTW, it work and need to revised the calculation. Thank you!

Collectives™ on Stack Overflow

Converting Excel Formula to SQL Syntax Calculation

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related