计算两个值之间的百分比 [英] 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.

这是有问题的代码.

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

我需要的是显示 SubmittedClaims 和 ApprovedClaims 列之间的百分比差异.任何列或两者都可能包含零，也可能不包含.

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.

所以它是: COUNT(Submitted.ClaimNumber) - COUNT(ApprovalProvision.ClaimNumber)/COUNT(Submitted.ClaimNumber) * 100 据我所知.

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

我已经尝试过这个，它所做的一个例子是当 1 和 117 之间的差异减少 99.15% 时，它需要 1 和 117 并返回 17.另一个例子是 2 和 100.这只是返回 0，而差异是减少了 98%.

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.

两个值之间的百分比差异

我也试过这个代码:

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

这以 2 和 100 为例，当实际差异是减少 98% 时返回 -4998.

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

为了完成，Submitted.ClaimNumber 是这部分代码:

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 是这样的:

ApprovalProvision.ClaimNumber is this:

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

理想情况下，此列也将处理 0.因此，如果一个值为 0，另一个值为 X，则结果应返回 0，因为如果原始数字为 0，则无法计算百分比.如果原始值为 X 且新值为 0，我应该显示减少100%.

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?

推荐答案

我不明白你为什么有 (x,0) 作为语法

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

但我看到你有

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

不应该，

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

看起来它会计算 ApprovalProvision.ClaimNumber - 100，因为 submit.claimnumber 除以自身是 1 乘以 100 是 100.

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

4900 数字听起来确实不错.让我们以下面的例子为例，你有 2 个苹果，然后你又得到了 98 个，得到了 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.

增加 98% 意味着从 2 个苹果，你会有 3.96 个苹果.

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

增加 100% 意味着您从 2 个苹果中得到 4 个苹果.增加 1000% 意味着您从 2 个苹果中得到 22 个苹果.所以 4000% 意味着你以 82 个苹果结束.5000% 表示从 2 个苹果中，您可以达到 102 个苹果.

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，所以看起来如果你从 2 个苹果开始，达到 100 个，苹果的数量会增加 4900%.

(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.

现在如果你翻转了 2 和 100，比如说从 100 开始，现在你有 2，(2-100)/100*100 = -98，所以苹果的变化为-98%，或者说减少了98%.

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.

这篇关于计算两个值之间的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！