CODEWITHSUNDEEP
sqlsql-server-2016percentage
Nick Kapatais
Nick Kapatais

Reputation: 336

Percent change returns wrong result

I've been at this for about 2 hours. I am trying to calculate the percentage change between two amounts. Here is the current code.

RejectedVsSubmittedSum = ((CASE WHEN COUNT(ClaimMain.ClaimNumber) > 0 THEN SUM(PCover.SumAssured) END
                                 -ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0))
                                 /NULLIF(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0))
                                 * 100

This line returns 225000.

CASE WHEN COUNT(ClaimMain.ClaimNumber) > 0 THEN SUM(PCover.SumAssured) END

This line returns 25000.

ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0)

The division.

NULLIF(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0)

Then I times by 100.

So 225000 - 25000 / 25000 * 100 returns 800. This is what I'm returning, but based on this site http://www.percent-change.com/index.php?y1=225000&y2=25000, my result is wrong and I should be returning -88.88888888 %.

I don't have any real experience in doing these calculations in SQL so I presume the website is correct. Can anybody see if I am actually doing anything wrong?

I've already asked this Calculate percentage between two values, but now I am using a different set of numbers, I'm now using amounts of money and not just counts. I've tried to go with the same logic (because it made sense to do so) but it seems like I'm returning the wrong result.

Upvotes: 0

Views: 95

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

According to the formula ((y2 - y1) / y1)*100, your query should be like this.

RejectedVsSubmittedSum = ( ( (ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0) ) 
    - (CASE WHEN COUNT(ClaimMain.ClaimNumber) > 0 THEN SUM(PCover.SumAssured) END) ) 
          / (CASE WHEN COUNT(ClaimMain.ClaimNumber) > 0 THEN SUM(PCover.SumAssured) END) ) * 100


y1: CASE WHEN COUNT(ClaimMain.ClaimNumber) > 0 THEN SUM(PCover.SumAssured) END
y2: ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0)

Upvotes: 0

Related Questions