sql-server - 存储过程 (SQL) 中的计算

Question

我必须在存储过程中为以下场景编写计算。我已经编写了以下代码，请告诉我它是否正确或者是否有其他更好的编写方法。

有一些“x”值的净值，我需要在以下条件下计算这个“x”值的佣金

1. 总 Assets 高达 5,000 英镑 - 30%
2. 总净 Assets 高达 5,000.01 英镑至 20,000 英镑 - 35%
3. 总净 Assets 高达 20,000.01 英镑至 50,000 英镑 - 40%
4. 总净 Assets 高达 50,000.01 英镑 + - 45%

例如

如果 NetWorth 为 100000，则计算如下

1. 对于 100000 人中的前 5000 人，佣金为 30%，即 5000 * 0.30 = 1500 人被排除在外 (95000)
2. 对于 95000 中的下一个 20000，佣金为 35%，即 20000 * 0.35 = 7000 被遗漏 (75000)
3. 对于 75000 中的下一个 50000，佣金为 40%，即 50000 * 0.40 = 20000 被遗漏 (25000)
4. 对于遗漏的 25000，佣金为 45%，即 25000 * 0.45 = 11250

所有这些佣金的总和 = point1 + point2 + point3 + point4 = 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 

Answer 1

是的，这可以改进。将“NetWorth”金额和佣金放在一个表中，然后使用 SQL 来完成工作:

declare @Comm table (
    NetworthLower float,
    NetWorthHigher float,
    Commission float
);

insert into @comm
    select 0, 5000, 0.30 union all
    select 5000, 20000, 0.35 union all
    select 20000, 50000, 0.4 union all
    select 50000, NULL, 0.45

declare @Value float = 8000;

select SUM(case when @Value >= c.NetWorthLower and @Value < coalesce(c.NetWorthHigher, @Value)
                then (@Value - c.NetWorthLower) * c.Commission
                when  @Value >= c.NetWorthLower
                then (c.NetWorthHigher - c.NetworthLower) * c.Commission
                else 0.0
           end)
from @Comm c 

这也使得计算整个表的佣金成为可能，而无需使用存储过程。