Unexpected result when adding computed column using case when

Question

I have a table in SQL Server as shown below. I'm computing the percentage change from value and prevvalue column

CREATE TABLE temp 
(
   [date] varchar(7),   
   [name] varchar(1),    
   [value] int,
   [prevvalue] int
)

INSERT INTO temp ([date], [name], [value], [prevvalue])
VALUES
   ('2018-10', 'A', 10, 23),
   ('2018-09', 'B', 45, 23),
   ('2018-10', 'C', 55, 11),
   ('2018-10', 'D', 0, 23),
   ('2018-09', 'D', 22,  0)

This is my query. I use CASE WHEN to handle the divide by zero issue.

SELECT
    [date],
    [name], 
    [value],
    [prevvalue],
    CASE
       WHEN [prevvalue] = 0 
          THEN ([value] - [prevvalue]) / ([prevvalue] + 1)
          ELSE ([value] - [prevvalue]) / [prevvalue]
    END AS pctChangFromLast 
FROM
    temp

However, what I got as results is:

date    name    value   prevvalue   pctChangFromLast
-------------------------------------------------------
2018-10 A       10         23        0.00000000000000
2018-09 B       45         23        0.00000000000000
2018-10 C       55         11        4.00000000000000
2018-10 D       0          23        -1.00000000000000
2018-09 D       22         0         2200.00000000000000

The pctChangFromLast of rows 1, 2, 4 is not right. What did I do wrong?

score 1 · Accepted Answer · answered Apr 13 '20 at 22:18

1

I recommend nullif() to avoid divide-by-zero. Then your problem is integer division. In SQL Server, 3/2 = 1, not 1.5.

I solve this by multiplying by 1.0:

([value] - [prevvalue]) * 1.0 / nullif([prevvalue], 0) AS pctChangFromLast

This returns NULL rather than some outrageous number when the previous value is 0.

answered Apr 13 '20 at 22:18

Gordon Linoff

1,242,037
58
646
786

I do need some large number when prevvalue is zero. What did I do wrong in my code? Why I got those percentage number wrong? thanks @Gordon Linoff – zesla Apr 13 '20 at 22:42
@zesla Need? I don't think so since you are completely misrepresenting the value. Does it make something else "easier" ? Perhaps. But let's be accurate and honest about the math here. – SMor Apr 13 '20 at 22:44
Yes, I do need in my work. I need to capture those change. That's confirmed. My question is what I did wrong in my code? – zesla Apr 13 '20 at 22:47
Let me clarify. I need to track number of social media mentions percent change from last week to this week. I need to rank them. I need to capture those not mentioned last week but current week. This is requested by our client. so need to do that. @SMor – zesla Apr 13 '20 at 22:58
1

@zesla If that is what your client wants, then that is your requirement. Equally you should explain to your client that this is mathematical nonsense and get some sort of official statement that this is desired. Something like this often comes back to bite the developer (consultant?) who "should have known and said something". – SMor Apr 13 '20 at 23:10
@zesla . . . This question is about calculating the ratio correctly with integers. I think this answers your question here. – Gordon Linoff Apr 13 '20 at 23:12
I do get your point. But in addition to your answer, could you also point out what I did wrong with my code to get what I originally wanted. I want to learn the column computation. Then I will accept your answer. @Gordon Linoff – zesla Apr 13 '20 at 23:19
@zesla . . . The problem with your code is the integer division. That is the second sentence in the answer. – Gordon Linoff Apr 14 '20 at 00:38

Unexpected result when adding computed column using case when

1 Answers1