我有两列保存数字,我试图计算它们之间的百分比差异并在另一列中显示结果,但结果似乎是错误的。
这是有问题的代码。
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
我需要的是显示“已提交的声明”和“已批准的声明”列之间的百分比差异。任何列或两者都可能包含零,也可能不包含零。
据我所知,它是: COUNT(Subscribed.ClaimNumber) - COUNT(ApprovalProvision.ClaimNumber) / COUNT(Subscribed.ClaimNumber) * 100。
我已经尝试过这个,它的作用的一个例子是,当 1 和 117 之间的差异减少 99.15% 时,它需要 1 和 117 并返回 17。另一个例子是 2 和 100。这只是返回 0,而差异是减少了 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
我检查了这个链接,这似乎就是我正在做的事情。
两个值之间的百分比差异 https://stackoverflow.com/questions/23637261/percentage-difference-between-two-values
我也尝试过这段代码:
NULLIF(COUNT(Submitted.ClaimNumber),0) - NULLIF(COUNT(ApprovalProvision.ClaimNumber),0)
/ NULLIF(COUNT(Submitted.ClaimNumber),0) * 100
以 2 和 100 为例,当实际差异减少 98% 时,返回 -4998。
为了完成,Submitted.ClaimNumber 是这部分代码:
LEFT OUTER JOIN (SELECT * FROM Company.Schema.ClaimMain WHERE CurrentStatus=10)Submitted
ON Submitted.ClaimNumber = ClaimMain.ClaimNumber
ApprovalProvision.ClaimNumber 是这样的:
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%。
这将在所有列中发生,但无需用其余列淹没页面,因为所有计算都将以相同的方式进行。
有人看到我做错了什么吗?