sql-server-2008 - 计算累计佣金金额

Question

我有一个包含以下内容的佣金分级表:

Lower    Upper     Percentage
0        300       45
300.01   800       50
800.01   1500      55

下限和上限金额是货币值，我需要根据总销售额计算要支付的累计金额，使用与总销售额相关的百分比金额。

如果我的总销售额为 350，我的佣金应计算如下:

总数的前 300 名将使用 45%剩下的 50 个将使用 50%所以我的总数是
300*45% = 135
50*50% = 25
总计 = 160

我正在通过存储过程更新包含金额的表格，因此需要在其中容纳该金额。

解决这个问题的最佳方法是什么？

注意:下面的存储过程具有正确的列名，在上面的示例中，为了简单起见，我更改了列名。 SPROC还加入了存储波段的表，更新表是一种工作/报告表

编辑:存储过程更新部分:

UPDATE CommissionCalculationDetails
SET TotalCommissionAmount = 
case
  when TotalSales > Upper then Upper
  when TotalSale > Lower then @sales - Lower
  else 0
end 
* Percentage / 100
FROM CommissionCalculationDetails
LEFT JOIN CommissionBand 
ON TotalSales > CommissionBand.Lower
AND TotalSales < CommisionBand.Upper

Answer 1

我建议您改为存储非包容性下限(例如:300 而不是 300.01)并在与它进行比较时严格使用大于。就目前而言，值 300.005 不会被正确分类。

如果是这种情况，您可以使用以下查询计算总佣金:

select
  sum (
    case
      when @sales > Upper then Upper
      when @sales > Lower then @sales - Lower
      else 0
    end 
    * Percentage / 100
  ) as TotalCommission
from CommissionTable

这是一个在线测试版本:http://www.sqlfiddle.com/#!3/87f12/8

---

稍微偏离主题:您的表格当前包含冗余信息；每个下限(或多或少)等于前一个上限。虽然这不是必需的，但您可以想出一种方法来存储，例如，仅存储上限(并且有一个 null 表示 unbounded)。

---

对于更新，一种可能的解决方案是在 function 中提取佣金计算，像这样:

create function ufn_CalculateCommission(@Sales money) 
  returns money 
as
begin

  declare @result money

  select 
    @result = 
      sum (
        case
          when @sales > Upper then Upper
          when @sales > Lower then @sales - Lower
          else 0
        end 
        * Percentage / 100
      )
  from CommissionBand

  return @result
end

声明函数后，更新可以简化为:

update CommissionCalculationDetails
set TotalCommissionAmount = dbo.ufn_CalculateCommission(TotalSales);

它是这样工作的:http://www.sqlfiddle.com/#!3/f4405/4