Celko SQL Puzzle: Calculating Commissions

I discussed a number of ways to split commissions among multiple salespeople in this article, but can you come up with other ways to keep track of the commission amounts? I'll give you a hint; ask yourself, "what is the simplest fact in this problem?"

One way of spotting a design problem is when you have to use a VIEW or CTE over and over to answer basic questions. This implies that the schema has spread information over many tables that should be in one table or that it has aggregated data in one row that should be split out into multiple rows.The schema is a mess. Attributes of a sales commission are split over multiple tables, so we ought to consolidate things in one place. The design below copies paper input forms that showed the total but did not breakdown the associated commissions.

CREATE TABLE SalesCommissions (sale_nbr INTEGER NOT NULL,  cust_id VARCHAR(10) NOT NULL,  salesperson_id VARCHAR(20) NOT NULL,  commission_amt DECIMAL(12,4) NOT NULL,  PRIMARY KEY (sale_nbr, cust_id, salesperson_id));

The total of each sale has to be derived in a VIEW. The VIEW will now balance to the commission amounts.

CREATE VIEW Sales (sale_nbr, cust_id, sales_amt) AS SELECT sale_nbr, cust_id, SUM(commission_amt)  FROM SalesCommissions  GROUP BY sale_nbr, cust_id;

One trick is to put the division of the spoils in a stored procedure that inserts an equal division into the SalesCommissions instead of trying to get it into a VIEW.

CREATE PROCEDURE PostCommissions (IN my_sale_nbr INTEGER,  IN my_cust_id VARCHAR(10),  IN my_sales_amt DECIMAL(12,4), --list of participating salespersons, say up to 5  IN salesperson_id_1 VARCHAR(20),  IN salesperson_id_2 VARCHAR(20),  IN salesperson_id_3 VARCHAR(20),  IN salesperson_id_4 VARCHAR(20),  IN salesperson_id_5 VARCHAR(20)) LANGUAGE SQL DETERMINISTIC BEGIN -- LOCAL TEMPORARY TABLE X ..ON COMMIT DELETE ROWS INSERT INTO X (salesperson_id) SELECT DISTINCT X.salesperson_id      FROM (VALUES (salesperson_id_1),        COALESCE (salesperson_id_2, salesperson_id_1),        COALESCE (salesperson_id_3, salesperson_id_1),        COALESCE (salesperson_id_4, salesperson_id_1),        COALESCE (salesperson_id_5, salesperson_id_1));

INSERT INTO SalesCommissions (sale_nbr, cust_id, salesperson_id, commission_amt)  SELECT my_sale_nbr, my_cust_id, X.salesperson_id,     my_sales_amt / (SELECT COUNT(*) FROM X)   FROM X; COMMIT; END;

It would be nice to make X into a CTE, but I do not know of any SQL product that can do that yet. This procedure is good for up to a five-away split. The pattern for adding more and more salespeople is obvious. The use of SELECT DISTINCT will also allow a salesperson's id to be input twice without causing an error. In the real world, there are probably more business rules for dividing the Spoils based on seniority, the items purchased and so forth.How many ways can you come up with to keep track of commission amounts?... One way of spotting a design problem is when you have to use a VIEW or CTE over and over to answer basic questions. This implies that the schema has spread information over many tables that should be in one table or that it has aggregated data in one row that should be split out into multiple rows.