CODEWITHSUNDEEP
sqlsql-serverexcel
S. Ideal
S. Ideal

Reputation: 15

Converting Excel Formula to SQL Syntax Calculation

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,

  1. On [Item No] Invalid Column Name. (Plenty error like this on certain name)
  2. On second WHEN Invalid Syntax.
  3. On CASE inside ELSE Invalid Syntax.
  4. 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

Upvotes: 1

Views: 1182

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

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

Upvotes: 1

Related Questions