CODEWITHSUNDEEP
sqlselectcase
RoyalGoat
RoyalGoat

Reputation: 33

SQL Case Value Switching

I am passing a series of double values in from another table ranging from -1 to 1. If the value is positive, I want to make it the difference between it and 1 (ie. 0.2 would then become .8). If value is negative, I want it to be the delta negative now above 1 (ie. 0.2 would be 1.2).

For some reason when I do this, it is taking the values from the previous table and making them fit the condition of the first WHEN (so in this setup, all the passed in values are being made positive and hitting the first WHEN).

Why isn't this working?

SELECT SnapshotDay
    ,SnapshotHour
    ,CASE
        WHEN Delta > 0.0 THEN (1 - Delta)
        WHEN Delta <= 0.0 THEN (abs(Delta) + 1)
        END as Adjust
FROM #Difference

Example:

-0.00118341262814151 -->  0.998816587371859
 0.00130811285278974 -->  0.99869188714721

Upvotes: 2

Views: 53

Answers (1)

D Stanley
D Stanley

Reputation: 152626

I'm not sure why you're seeing the results you are, but mathematically your expected result is just 1 - Delta:

SELECT SnapshotDay
    ,SnapshotHour
    ,(1 - Delta) as Adjust
FROM #Difference

If Delta is positive it will be substracted from 1:

(1 - Delta)

If Delta is negative the absolute value will be added to 1:

(-1*Delta + 1) == (1 - Delta)

Upvotes: 2

Related Questions