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, Apr 06 '18 at 02: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, Apr 06 '18 at 02:32
Please show the definition of `myTable` - all fields and data types. Thanks. — Bob Jarvis - Слава Україні, Apr 06 '18 at 02: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, Apr 06 '18 at 02: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, Apr 06 '18 at 02:38
@maSTAShuFu I'm using SSMS 17, server says Microsoft SQL Server Standard (64-bit) Version 11.0.6251.0 — S. Ideal, Apr 06 '18 at 03:32

RoMEoMusTDiE · Accepted Answer · 2018-04-06T03:46:24.980

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

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! — S. Ideal, Apr 06 '18 at 06:22

Converting Excel Formula to SQL Syntax Calculation

1 Answers1