CODEWITHSUNDEEP
sqlsql-server-2016percentage
Nick Kapatais
Nick Kapatais

Reputation: 336

Calculate percentage between two values

I have two columns that hold numbers for which I am trying to calculate the difference in % between and show the result in another column but the results seem to be wrong.

This is the code in question.

SELECT 
    GenPar.ParameterValue AS ClaimType,
    COUNT(Submitted.ClaimNumber) AS SubmittedClaims,
    COUNT(ApprovalProvision.ClaimNumber) AS ApprovedClaims,
    COUNT(Declined.ClaimNumber) AS DeclinedClaims,
    COUNT(Pending.ClaimNumber) AS PendingClaims,
    ISNULL(SUM(SubmittedSum.SumInsured),0) AS TotalSubmittedSumInsured,
    ISNULL(SUM(ApprovedSum.SumInsured),0) AS TotalApprovedSumInsured,
    ISNULL(SUM(RejectedSum.SumInsured),0) AS TotalRejectedSumInsured,
    ISNULL(SUM(PendingSum.SumInsured),0) AS TotalPendingSumInsured,
    --This column is to show the diff in %
    CASE WHEN COUNT(Submitted.ClaimNumber) <> 0 AND COUNT(ApprovalProvision.ClaimNumber) <> 0
         THEN (COUNT(ApprovalProvision.ClaimNumber),0) - (COUNT(Submitted.ClaimNumber),0)
              /COUNT(Submitted.ClaimNumber) * 100
         ELSE 0
         END

What I need is to show the difference in % between the columns SubmittedClaims and ApprovedClaims. Any column, or both may contain zeroes and it may not.

So it's: COUNT(Submitted.ClaimNumber) - COUNT(ApprovalProvision.ClaimNumber) / COUNT(Submitted.ClaimNumber) * 100 as far as I know.

I have tried this and an example of what it does is it takes 1 and 117 and returns 17 when the difference between 1 and 117 is a decrease of 99.15%. Another example is 2 and 100. This simply returns 0 whereas the difference is a decrease of 98%.

CASE WHEN COUNT(Submitted.ClaimNumber) <> 0 AND COUNT(ApprovalProvision.ClaimNumber) <> 0
         THEN (COUNT(ApprovalProvision.ClaimNumber),0) - (COUNT(Submitted.ClaimNumber),0)
              /COUNT(Submitted.ClaimNumber) * 100
         ELSE 0
         END

I've checked this link and this seems to be what I am doing.

Percentage difference between two values

I've also tried this code:

NULLIF(COUNT(Submitted.ClaimNumber),0) - NULLIF(COUNT(ApprovalProvision.ClaimNumber),0) 
    / NULLIF(COUNT(Submitted.ClaimNumber),0) * 100

and this takes for example 2 and 100 and returns -4998 when the real difference is a decrease of 98%.

For completion, Submitted.ClaimNumber is this portion of code:

LEFT OUTER JOIN (SELECT * FROM Company.Schema.ClaimMain WHERE CurrentStatus=10)Submitted
ON Submitted.ClaimNumber = ClaimMain.ClaimNumber

ApprovalProvision.ClaimNumber is this:

LEFT OUTER JOIN (SELECT * FROM Company.Schema.ClaimMain WHERE CurrentStatus=15)ApprovalProvision
ON ApprovalProvision.ClaimNumber = ClaimMain.ClaimNumber

Ideally, this column would also deal with 0's. So if one value is 0 and the other is X, the result should return 0 since a percentage can't be calculated if original number is 0. If the original value is X and the new value is 0, I should show a decrease of 100%.

This will occur across all columns but there is no need to flood the page with the rest of the columns since all calculations will occur in the same manner.

Anybody see what I'm doing wrong?

Upvotes: 3

Views: 4971

Answers (1)

Michael C. Chen
Michael C. Chen

Reputation: 201

I'm not familiar with why you have (x,0) as a syntax

But I see that you have 

(COUNT(ApprovalProvision.ClaimNumber),0) - (COUNT(Submitted.ClaimNumber),0)
              /COUNT(Submitted.ClaimNumber) * 100

shouldn't it be, 

( COUNT(ApprovalProvision.ClaimNumber) - COUNT(Submitted.ClaimNumber) )
              /COUNT(Submitted.ClaimNumber) * 100

It looks like it would do count of ApprovalProvision.ClaimNumber - 100 since submitted.claimnumber divided by itself is 1 times 100 is 100.

The 4900 number actually sounds right. Lets take the following example, you have 2 apples, and then you're given 98 more and got 100 apples.

An increase of 98% would have meant from 2 apples, you would have 3.96 apples.

An increase of 100% means from 2 apples you end with 4 apples. An increase of 1000% means from 2 apples you end with 22 apples. So 4000% means you end with 82 apples. 5000% means from 2 apples, you reach 102 apples.

(100-2)/2*100 = 98 / 2 = 49 * 100 = 4900, so it looks like there is a 4900% increase in number of apples if you started with 2 apples and reach 100.

Now if you had flipped the 2 and 100, say starting with 100, now you have 2, (2-100)/100*100 = -98, so a -98% change of apples, or a 98% decrease.

Hope this solves your problem.

Upvotes: 1

Related Questions