我必须在存储过程中为以下情况编写一个计算。我已经编写了以下代码，请让我知道它是正确的还是还有其他更好的编写方法。
NetWorth量有一些“ x”值，在以下情况下，我需要计算该“ x”值的佣金
净资产总额高达5,000英镑-30％
NetWorth总计不超过£5,000.01至£20,000-35％
NetWorth总额从£20,000.01到£50,000-40％
NetWorth总计不超过£50,000.01 +-45％
例如
如果NetWorth为100000，则计算如下
对于100000中的前5000个，佣金为30％，即5000 * 0.30 = 1500（95000）
对于95000的下一个20000，佣金为35％，即20000 * 0.35 = 7000（75000）
对于75000中的下一个50000，佣金为40％，即50000 * 0.40 = 20000（25000）
对于剩下的25000，佣金为45％，即25000 * 0.45 = 11250
所有这些佣金的总和=点1 +点2 +点3 +点4 = 1500 + 7000 + 20000 + 11250 = 39750
以下是我编写的存储过程中的代码。请让我知道这是否可以改进或有其他写方法。
DECLARE @NetWorth DECIMAL(18, 2)
DECLARE @InterMediateTier1Value DECIMAL(18, 2)
DECLARE @InterMediateTier2Value DECIMAL(18, 2)
DECLARE @InterMediateTier3Value DECIMAL(18, 2)
DECLARE @InterMediateTier1Commission DECIMAL(18, 2)
DECLARE @InterMediateTier2Commission DECIMAL(18, 2)
DECLARE @InterMediateTier3Commission DECIMAL(18, 2)
DECLARE @RemainderCommission DECIMAL(18, 2)
DECLARE @RemainderValue DECIMAL(18, 2)
SET @NetWorth = 40000
DECLARE @TotalCommission DECIMAL(18, 2)
IF @NetWorth <= 5000
BEGIN
SET @InterMediateTier1Commission = @NetWorth * 0.30
SET @TotalCommission = @InterMediateTier1Commission
END
ELSE IF @NetWorth > 5000
AND @NetWorth <= 20000
BEGIN
SET @InterMediateTier2Value = @NetWorth - 5000
SET @InterMediateTier1Commission = 5000 * 0.30
SET @InterMediateTier2Commission = @InterMediateTier2Value * 0.35
SET @TotalCommission = @InterMediateTier1Commission
+ @InterMediateTier2Commission
END
ELSE IF @NetWorth > 20000
AND @NetWorth <= 50000
BEGIN
SET @InterMediateTier1Value = @NetWorth - 5000
SET @InterMediateTier1Commission = 5000 * 0.30
IF @InterMediateTier1Value > 20000
SET @RemainderValue = @InterMediateTier1Value - 20000
SET @RemainderCommission = @RemainderValue * 0.40
SET @InterMediateTier2Commission = 20000 * 0.35
SET @TotalCommission = @InterMediateTier1Commission
+ @InterMediateTier2Commission
+ @RemainderCommission
END
ELSE IF @NetWorth > 50000
BEGIN
SET @InterMediateTier1Value = @NetWorth - 5000
SET @InterMediateTier1Commission = 5000 * 0.30
IF @InterMediateTier1Value > 20000
SET @RemainderValue = @InterMediateTier1Value - 20000
SET @InterMediateTier2Commission = 20000 * 0.35
IF @RemainderValue > 50000
SET @InterMediateTier4Value = @RemainderValue - 50000
SET @InterMediateTier3Commission = 50000 * 0.40
SET @RemainderCommission = @RemainderValue * 0.45
SET @TotalCommission = @InterMediateTier1Commission
+ @InterMediateTier2Commission
+ @InterMediateTier3Commission
+ @RemainderCommission
END
SELECT @TotalCommission AS TotalCommission
最佳答案
试试这个，我发现它适用于以下测试案例
测试案例1：DECLARE @NetWorth DECIMAL（18，2）= 1000
测试案例2：DECLARE @NetWorth DECIMAL（18，2）= 9999
测试案例3：声明@NetWorth DECIMAL（18，2）= 40000
测试案例4：宣告@NetWorth DECIMAL（18，2）= 78000
询问
DECLARE @NetWorth DECIMAL(18, 2) = 488000
SELECT TotalCommission =
CONVERT(DECIMAL(18, 2),
CASE WHEN @NetWorth <= 5000 THEN @NetWorth * 0.30
WHEN @NetWorth > 5000 AND @NetWorth <= 20000 THEN (5000 * 0.30) + (@NetWorth - 5000) * 0.35
WHEN @NetWorth > 20000 AND @NetWorth <= 50000
THEN CASE WHEN ((@NetWorth - 5000) > 20000)
THEN (5000 * 0.30) +
(20000 * 0.35) +
((@NetWorth - 5000)- 20000)* 0.40
ELSE (5000 * 0.30)+ (20000 * 0.35)
END
WHEN @NetWorth > 50000
THEN CASE WHEN ((@NetWorth - 5000) > 20000)
THEN (5000 * 0.30) +
(20000 * 0.35) +
(50000 * 0.40) +
((@NetWorth - 5000) - 20000 )*0.45
ELSE (5000 * 0.30) + (20000 * 0.35) + (50000 * 0.40)
END
END
)
希望这可以帮助。让我知道它是否在任何情况下都失败。