Calculate Tiered Commissions

Business Case: The sales team has a tiered commission structure in place such that the first two item sales (quantity two) of a given product to a single customer are commissioned at one rate and all subsequent sales of the product to the same customer are commissioned at a second rate. Furthermore, there is a value floor such that if a product is sold below a specified value, the difference between the sales value and the value floor is deducted from the commission. The sales team needs a report to calculate commission automatically, by salesperson, by sales order line.

Environment: Sales orders consist of a header record containing customer information and line records containing details about each item sold. The line records include an item number, sales quantity, and sales value. The complication with this business request is that product sales can span multiple sales orders and a single sales line could have any quantity. The result of these two factors is that no single sales order header or line record is sufficient to identify what the sales commission would be. Nevertheless, commission amounts must be discretely tracked by customer product unit (sales order line) to ensure value floor and tiering rules are accurately enforced.

Approach: To address this business requirement, we will aggregate product sales per customer product, then split the aggregate quantity into individual records with a sufficiently deterministic sort such that commission can be calculated and applied at the sales order line level.

Demonstration Preparation: For demonstration purposes, first we will simulate the underlying data structure by creating three dimensional tables: customers, salespersons, and products (including commission structure information). We will then create a parent table called SalesOrders and a child table called SalesOrderLines, related to each other through a foreign key. For this example we will assume our database is named “DB” and will be working within the “dbo” schema.

-- If the customer foreign key exists, drop it
IF OBJECT_ID('DB.dbo.FK_SalesOrders_dimCustomers') IS NOT NULL
    ALTER TABLE DB.dbo.SalesOrders
    DROP CONSTRAINT FK_SalesOrders_dimCustomers
;

-- If the customer table exists, drop it
IF OBJECT_ID('DB.dbo.dimCustomers') IS NOT NULL
    DROP TABLE DB.dbo.dimCustomers
;

-- Create the customer table
CREATE TABLE DB.dbo.dimCustomers(
    ID INTEGER IDENTITY(1,1) NOT NULL
    , CustomerName VARCHAR(50) NOT NULL
    , CONSTRAINT PK_dimCustomers PRIMARY KEY NONCLUSTERED(ID)
) ON [PRIMARY]
;

-- Populate the customer table
INSERT INTO DB.dbo.dimCustomers (
    CustomerName)
VALUES
    ('ABC Supply')
    ('DEF Enterprises')
    , ('XYZ Corporation')
;

-- Display the population of the Customer table
SELECT *
FROM DB.dbo.dimCustomers
;

-- If the salesperson foreign key exists, drop it
IF OBJECT_ID('DB.dbo.FK_SalesOrders_dimSalespersons') IS NOT NULL
    ALTER TABLE DB.dbo.SalesOrders
    DROP CONSTRAINT FK_SalesOrders_dimSalespersons
;

-- If the salesperson table exists, drop it
IF OBJECT_ID('DB.dbo.dimSalespersons') IS NOT NULL
    DROP TABLE DB.dbo.dimSalespersons
;

-- Create the salesperson table
CREATE TABLE DB.dbo.dimSalespersons (
    ID INTEGER IDENTITY(1,1) NOT NULL
    , SalespersonName VARCHAR(50) NOT NULL
    , CONSTRAINT PK_dimSalespersons PRIMARY KEY NONCLUSTERED (ID)
) ON [PRIMARY]
;

-- Populate the salesperson table
INSERT INTO DB.dbo.dimSalespersons (
    SalespersonName)
VALUES
    ('Willy Loman')
    ('Richard Roma')
;

-- Display the population of the salesperson table
SELECT *
FROM DB.dbo.dimSalespersons
;

-- If the products foreign key exists, drop it
IF OBJECT_ID('DB.dbo.FK_SalesOrderLines_dimProducts') IS NOT NULL
    ALTER TABLE DB.dbo.SalesOrderLines
    DROP CONSTRAINT FK_SalesOrderLines_dimProducts
;

-- If the products table exists, drop it
IF OBJECT_ID('DB.dbo.dimProducts') IS NOT NULL
    DROP TABLE DB.dbo.dimProducts
;

-- Create the products table
CREATE TABLE DB.dbo.dimProducts (
    ID INTEGER IDENTITY(1,1) NOT NULL
    , ProductName VARCHAR(50) NOT NULL
    , FloorPrice MONEY NOT NULL
    , TierOneQuantity TINYINT NOT NULL
    , TierOneCommissionRate DECIMAL(3,2) NOT NULL
    , TierTwoCommissionRate DECIMAL(3,2) NOT NULL
    , CONSTRAINT PK_dimProducts PRIMARY KEY NONCLUSTERED (ID)
) ON [PRIMARY]
;

-- Populate the products table
INSERT INTO DB.dbo.dimProducts (
    ProductName
    , FloorPrice
    , TierOneQuantity
    , TierOneCommissionRate
    , TierTwoCommissionRate)
VALUES
    ('Widget'45, 2, 0.1, 0.15)
    , ('Gadget', 30, 5, 0.05, 0.1)
    , ('Thing-a-ma-bob', 12, 10, 0.10, 0.12)
;

-- Display the population of the products table
SELECT *
FROM DB.dbo.dimProducts
;

-- If the sales order line table foreign key exists, drop it
IF OBJECT_ID('DB.dbo.FK_SalesOrderLines_SalesOrders') IS NOT NULL
    ALTER TABLE DB.dbo.SalesOrderLines
    DROP CONSTRAINT FK_SalesOrderLines_SalesOrders
;

-- If the sales order table exists, drop it
IF OBJECT_ID('DB.dbo.SalesOrders') IS NOT NULL
    DROP TABLE DB.dbo.SalesOrders
;

-- Create the sales order table
CREATE TABLE DB.dbo.SalesOrders (
    ID INTEGER IDENTITY(1,1) NOT NULL
    , CustomerID INTEGER NOT NULL
    , SalespersonID INTEGER NOT NULL
    , OrderDate DATE NOT NULL
    , CONSTRAINT PK_SalesOrders PRIMARY KEY NONCLUSTERED (ID)
) ON [PRIMARY]
;

-- Add the customer foreign key to the sales order table
ALTER TABLE DB.dbo.SalesOrders
ADD CONSTRAINT FK_SalesOrders_dimCustomers
    FOREIGN KEY(CustomerID)
    REFERENCES DB.dbo.dimCustomers(ID)
;

-- Add the salesperson foreign key to the sales order table
ALTER TABLE DB.dbo.SalesOrders
ADD CONSTRAINT FK_SalesOrders_dimSalespersons
    FOREIGN KEY(SalespersonID)
    REFERENCES DB.dbo.dimSalespersons(ID)
;

-- Populate the sales order table
INSERT INTO DB.dbo.SalesOrders (
    CustomerID
    , SalesPersonID
    , OrderDate)
VALUES
    (1, 1, GETDATE())
    , (1, 1, DATEADD(DAY1, GETDATE()))
    , (3, 2, GETDATE())
;

-- Display the population of the sales order table
SELECT *
FROM DB.dbo.SalesOrders
;

-- If the sales order line table exists, drop it
IF OBJECT_ID('DB.dbo.SalesOrderLines') IS NOT NULL
    DROP TABLE DB.dbo.SalesOrderLines
;

-- Create the sales order line table
CREATE TABLE DB.dbo.SalesOrderLines (
    ID INTEGER IDENTITY(1,1) NOT NULL
    , SalesOrderID INTEGER NOT NULL
    , ProductID INTEGER NOT NULL
    , Quantity INTEGER NOT NULL
    , UnitSalePrice MONEY NOT NULL
    , CONSTRAINT PK_SalesOrderLines PRIMARY KEY NONCLUSTERED (ID)
) ON [PRIMARY]
;

-- Add the foreign key to the sales order line table
ALTER TABLE DB.dbo.SalesOrderLines
ADD CONSTRAINT FK_SalesOrderLines_SalesOrders
    FOREIGN KEY(SalesOrderID)
    REFERENCES DB.dbo.SalesOrders(ID)
;

-- Add the products foreign key to the sales order table
ALTER TABLE DB.dbo.SalesOrderLines
ADD CONSTRAINT FK_SalesOrderLines_dimProducts
    FOREIGN KEY(ProductID)
    REFERENCES DB.dbo.dimProducts(ID)
;

-- Populate the sales order line table
INSERT INTO DB.dbo.SalesOrderLines (
    SalesOrderID
    , ProductID
    , Quantity
    , UnitSalePrice)
VALUES
    ( 1, 1, 1, 50)
    , (1, 2, 4, 25)
    , (1, 3, 12, 15)
    , (2, 1, 5, 45)
    , (2, 3, 15, 20)
    , (3, 1, 3, 44)
;

-- Display the population of the sales order line table
SELECT *
FROM DB.dbo.SalesOrderLines
;

To see the basic information we have inserted, run the following SELECT statement.

SELECT
    dimSalespersons.SalespersonName
    , dimCustomers.CustomerName
    , SalesOrderLines.SalesOrderID
    , SalesOrderLines.ID AS SalesOrderLineID
    , dimProducts.ProductName
    , SalesOrderLines.Quantity
    , SalesOrderLines.UnitSalePrice
    , TotalSalePrice =
        SalesOrderLines.Quantity
        * SalesOrderLines.UnitSalePrice
FROM
    DB.dbo.SalesOrders
    INNER JOIN DB.dbo.SalesOrderLines
        ON SalesOrders.ID SalesOrderLines.SalesOrderID
    INNER JOIN DB.dbo.dimCustomers
        ON SalesOrders.CustomerID dimCustomers.ID
    INNER JOIN DB.dbo.dimSalespersons
        ON SalesOrders.SalespersonID = dimSalespersons.ID
    INNER JOIN DB.dbo.dimProducts
        ON SalesOrderLines.ProductID = dimProducts.ID
;

Solution: The first step is to expand the sales order lines such that there is one record per sold product quantity. In other words, if a single sales order line has a sold quantity of three (3) units, then that record will be triplicated such that there are three (3) records with an assumed quantity of one (1). When we split the sales order line we will assign each unit record with an ordinality, thereby allowing us to uniquely identify each item sold. Note that the actual sort of items within a sales order line is not material; it is sufficient that there is only one first sold item, one second sold item, and so on. Within the sales order line it does not matter which is first as they are all the same. Between sales orders, however, it does matter as the products could have different unit sale prices — thus the sort must be deterministic on the sales order.

The following code creates our first view to split the sales order lines into individual units sold and assign unit ordinality.

-- Expand quantities to calculate tiered commission
CREATE VIEW
 dbo.vwCalculateTieredCommissions
AS
SELECT
    dimSalespersons.SalespersonName
    , dimCustomers.CustomerName
    , dTbl.ID AS SalesOrderNumber
    , dimProducts.ProductName
    , dTbl.Quantity AS SalesOrderLineQuantity
    , dTbl.SaleOrdinality
    , dTbl.UnitSalePrice
    , CommissionRate =
        CASE
            WHEN dTbl.SaleOrdinality <= dimProducts.TierOneQuantity
                THEN dimProducts.TierOneCommissionRate
            ELSE dimProducts.TierTwoCommissionRate
            END
    , CommissionableSalePrice =
        CASE
            WHEN dTbl.UnitSalePrice >= dimProducts.FloorPrice
                THEN dTbl.UnitSalePrice
            ELSE dimProducts.FloorPrice
            END
    , CommissionModifier =
        CASE
            WHEN dTbl.UnitSalePrice >= dimProducts.FloorPrice
                THEN 0
            ELSE dTbl.UnitSalePrice - dimProducts.FloorPrice
            END
FROM (
        -- Expand the sales order lines such that there is
        -- a record for each unit sold
        SELECT
            SalesOrders.ID
            , SalesOrders.CustomerID
            , SalesOrders.SalespersonID
            , SalesOrderLines.ProductID
            , SalesOrderLines.Quantity
            , SalesOrderLines.UnitSalePrice
            -- Rank the records by sales order date then
            -- by sales order unique identifier
            , SaleOrdinality =
                ROW_NUMBER() OVER (
                    PARTITION BY
                        SalesOrders.CustomerID
                        , SalesOrderLines.ProductID
                    ORDER BY
                        SalesOrders.OrderDate
                        , SalesOrders.ID)
        FROM
            DB.dbo.SalesOrders
            INNER JOIN DB.dbo.SalesOrderLines
                ON SalesOrders.ID SalesOrderLines.SalesOrderID
            CROSS APPLY (
                SELECT n
                FROM DB.dbo.utfNumbers(SalesOrderLines.Quantity)
            ) AS Multiplier
        ) AS dTbl
        INNER JOIN DB.dbo.dimProducts
            ON dTbl.ProductID dimProducts.ID
        INNER JOIN DB.dbo.dimCustomers
            ON dTbl.CustomerID dimCustomers.ID
        INNER JOIN DB.dbo.dimSalespersons
            ON dTbl.SalespersonID dimSalespersons.ID
;

Finally, we create a view to aggregate the line-level values of the previous view.

-- Aggregate unit-level commission calculation
CREATE VIEW dbo.vwAggregateTieredCommissions
AS
SELECT
    SalespersonName
    , CustomerName
    , SalesOrderNumber
    , ProductName
    , SUM(UnitSalePrice) AS SumOfSalePrice
    , SumOfCommission CONVERT(MONEY,
        SUM(
            (CommissionRate
            * CommissionableSalePrice)
            + CommissionModifier))
FROM
    DB.dbo.vwCalculateTieredCommissions
GROUP BY
    SalespersonName
    , CustomerName
    , SalesOrderNumber
    , ProductName
;

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • Mike Magnusson  On 06 Apr 2012 at 2117

    I don’t understand most of this Lexton, but seems pretty damn impressive. When are you moving back to Ohio?

    • lextonr  On 09 Apr 2012 at 0824

      I’ll be up that way this summer for a week or two. 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: