计算累计佣金数额

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

Total = 160

我正在通过sproc更新一个包含金额的表，因此需要在其中容纳这一点。

怎么做才是最好的办法？

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

编辑: 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表示无界)。

对于更新，一个可能的解决方案是提取函数中的佣金计算，如下所示：

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