sql-server - 在值范围层上分配值

Question

我有两个表:每个员工的总销售额列表和薪酬等级列表

Employee Sales  |   Comp Tiers
==============  |   ===================
EmpID  Sales    |   TierID MaxAmt  Rate
  1    12000    |     1     10000   20%  --  Up to $10k sales  compensated at 20%
  2    17000    |     2     15000   25%  --The next $5k sales  compensated at 25%
  3    23000    |     3     20000   30%  --The next $5k sales  compensated at 30%
  4    31000    |     4     25000   40%  --The next $5k sales  compensated at 40%
                |     5     99999   50%  --Any remaining sales compensated at 50%

基于这些输入，我需要根据每个层级的 MaxAmt 值将每个员工的销售额拆分到每个层级，以计算每个层级的薪酬率。此外，我不想对每一层的计算进行硬编码，因为层数可能会随时间变化。 (转念一想，我不介意硬编码，只要它可以处理最多 5 层。听起来公平吗？)

期望的输出:

EmpID  Sales  TierID  TierAmt  Rate   Net
=========================================
  1    12000    1      10000    20%  2000
  1    12000    2       2000    25%   500
  2    17000    1      10000    20%  2000
  2    17000    2       5000    25%  1250
  2    17000    3       2000    30%   600
  3    23000    1      10000    20%  2000
  3    23000    2       5000    25%  1250
  3    23000    3       5000    30%  1500
  3    23000    4       3000    40%  1200
  4    31000    1      10000    20%  2000
  4    31000    2       5000    25%  1250
  4    31000    3       5000    30%  1500
  4    31000    4       5000    40%  2000
  4    31000    5       6000    50%  3000

我对 SQL 并不陌生，但我什至无法理解合适的策略。有任何想法吗？如果有助于实现目标，则允许更改表结构。

SQLFiddle

Answer 1

让我们做一些测试数据:

DECLARE @EmpSales TABLE
(
    EmpID INT,
    Sales INT
)

INSERT INTO @EmpSales
VALUES
( 1, 12000 ),
( 2, 17000 ),
( 3, 23000 ),
( 4, 31000 );

DECLARE @CompTiers TABLE
(
    TierID INT,
    MaxAmount INT,
    Rate DECIMAL(10,2)
)

INSERT INTO @CompTiers
VALUES
( 1, 10000, .20 ),
( 2, 15000, .25 ),
( 3, 20000, .30 ),
( 4, 25000, .40 ),
( 5, 99999, .50 );

在这里，我创建了一个 CTE 来查找所有层和之前的层(以获得层的顶部和底部)

WITH Tiers AS
(
    SELECT 
        n.TierID,
        n.MaxAmount,
        n.Rate,
        ISNULL(p.MaxAmount, 0) PrevAmount
    FROM @CompTiers n
    LEFT JOIN @CompTiers p
        ON p.TierID = n.TierID - 1
), 

让我们采用 CTE 层并将其与销售额交叉连接，仅选择销售额大于 prevamount 的层(层底部)。

SalesComp AS
(
    SELECT *
    FROM @EmpSales e
    CROSS JOIN Tiers c
    WHERE Sales > PrevAmount
)

现在我们已经匹配了数据，只需用一些案例清理它:

SELECT 
    s.EmpID,
    s.Sales,
    s.TierID,
    CASE 
        WHEN s.Sales > s.MaxAmount THEN s.MaxAmount - s.PrevAmount 
        ELSE s.Sales - s.PrevAmount 
    END TierAmount,
    s.Rate,
    CASE 
        WHEN s.Sales > s.MaxAmount THEN (s.MaxAmount - s.PrevAmount) * s.Rate
        ELSE (s.Sales - s.PrevAmount) * s.Rate
    END Net
FROM SalesComp s
ORDER BY EmpID, TierID

这是输出:

EmpID   Sales   TierID  TierAmount  Rate    Net
1   12000   1   10000   0.20    2000.00
1   12000   2   2000    0.25    500.00
2   17000   1   10000   0.20    2000.00
2   17000   2   5000    0.25    1250.00
2   17000   3   2000    0.30    600.00
3   23000   1   10000   0.20    2000.00
3   23000   2   5000    0.25    1250.00
3   23000   3   5000    0.30    1500.00
3   23000   4   3000    0.40    1200.00
4   31000   1   10000   0.20    2000.00
4   31000   2   5000    0.25    1250.00
4   31000   3   5000    0.30    1500.00
4   31000   4   5000    0.40    2000.00
4   31000   5   6000    0.50    3000.00