CODEWITHSUNDEEP
sql-server
mikebmassey
mikebmassey

Reputation: 8594

SQL Server - Calculating Percentage Change with 0 CASE

I'm trying to calculate percentage change from 2 columns. Since you will get an error when you divide by 0, I wanted to create a CASE that would automatically handle 0s, then handle non-zeros.

The behavior I am trying to implement is: If StartValue is 0, then the possible outcomes are 0 or 1 (since the current value is either positive or stayed the same). If the StartValue is not 0, then get the percentage change, but limit the result to between -1 and +1. 

UPDATE #Temp
SET ValueChange = 
CASE 
    WHEN StartValue = 0 THEN (CASE
        WHEN CurrentValue = 0 THEN 0
        WHEN CurrentValue > 1 THEN 1
        ELSE 0
        END)
    ELSE 
    WHEN ((CurrentValue - StartValue)/StartValue) > 1 THEN 1
    WHEN ((CurrentValue - StartValue)/StartValue) < -1 THEN -1
    ELSE ((CurrentValue - StartValue)/StartValue)
END;

When I run this query, I get this error: Incorrect syntax near the keyword 'WHEN'., which references the WHEN ((CurrentValue - StartValue)/StartValue) > 1 THEN 1 section of the code.

Thoughts on the best way to implement this logic?

Upvotes: 0

Views: 920

Answers (1)

Francis P
Francis P

Reputation: 13665

The correct syntax should be:

UPDATE Temp
SET ValueChange = 
  CASE 
    WHEN StartValue = 0 THEN (
      CASE
        WHEN CurrentValue = 0 THEN 0
        WHEN CurrentValue > 1 THEN 1
        ELSE 0
      END)
    ELSE 
      CASE 
        WHEN ((CurrentValue - StartValue)/StartValue) > 1 THEN 1
        WHEN ((CurrentValue - StartValue)/StartValue) < -1 THEN -1
        ELSE ((CurrentValue - StartValue)/StartValue)
      END
  END;

Upvotes: 1

Related Questions