asked on

Tiered commission calculation in SQL - How to calculate?

In a company there are 2 groups of people. One group gets paid on commission 50% for all gross revenue they bring in. Another group gets paid on commission, but on a tiered basis. That is, someone can make:

50% on 0 to 100,000
55% on 100,001 to 250,000
60% on 250,001 and above

Someone in the tiered group has been working since Jan 1 and has passed the 150,000 mark on March 15th. The current date is April 30 and his total gross revenues year to date are 300,500. This is just one person in the group. Others have different total gross revenues year to date.

How would you structure the DB and calculate their total commissions year to date?

cmilleruk

It largely depends on what happens if you change the tier structure halfway through the year. Would you want the commision earnt to be recalculated or not?

e.g. Using your example, if at the end of May, you change the tiers to the following:

50% on 0 to 150,000
55% on 150,001 to 300,000
60% on 300,001 and above

...would this affect the commision that has already been earnt?

Regards

tmyint

ASKER

Assume for now that the tiers would remain static.

If ever there was a change, the data would first be archived before any recalculations were made.

cmilleruk

In this instance, I would use a function to calculate the commission as shown below. You could also modify this function to retrieve the tiered values from a table:

Usage: SELECT dbo.calculateCommission( [GrossRevenueToDate], [bitUseTieredBasis])

CREATE FUNCTION dbo.calculateCommission
(
@grossRevenue money,
@tiered bit
)
RETURNS money
AS
BEGIN

IF @tiered = 0
      RETURN @grossRevenue * (0.5)

---(else)
DECLARE @commission money

SET @commission = 0

IF @grossRevenue <= 100000
      SET @commission = @commission + (@grossRevenue * (0.5))
ELSE
      SET @commission = @commission + (100000 * (0.5))

SET @grossRevenue = @grossRevenue - 100000

IF @grossRevenue <= 150000 --Difference between 100,000 and 250,000
      SET @commission = @commission + (@grossRevenue * (0.55))
ELSE
      SET @commission = @commission + (150000 * (0.55))

SET @grossRevenue = @grossRevenue - 150000

SET @commission = @commission + (@grossRevenue * (0.6))

RETURN @commission

END

tmyint

ASKER

Thanks - but instead of using hardcoded values -- how would you retrieve the amounts and associated values from a table?

cmilleruk

I'm afraid I have to head off for a meeting but I will take a look tomorrow for you.

cmilleruk

/*

DROP TABLE dbo.commissionTABLE

CREATE TABLE dbo.commissionTable
(
intCommissionType int,
intThresholdStart int,
intThresholdEnd int,
fltCommissionPct float
)

INSERT INTO commissionTable VALUES (0,0,null,0.5)
INSERT INTO commissionTable VALUES (1,0,100000,0.5)
INSERT INTO commissionTable VALUES (1,100000,250000,0.55)
INSERT INTO commissionTable VALUES (1,250000,null,0.6)

*/

ALTER FUNCTION dbo.calculateCommission
(
@grossRevenue money,
@commissionType int
)
RETURNS money
AS
BEGIN

DECLARE @commission money

SET @commission = 0

DECLARE @lower int
DECLARE @upper int
DECLARE @threscomm float

DECLARE c_Tier CURSOR FOR SELECT intThresholdStart, intThresholdEnd, fltCommissionPct
FROM dbo.commissionTable WHERE intCommissionType = @commissionType
ORDER BY intThresholdStart

OPEN c_Tier

FETCH NEXT FROM c_Tier INTO @lower, @upper, @threscomm

WHILE @@FETCH_STATUS = 0
BEGIN
      IF ISNULL(@upper, 1) = 1
      BEGIN
            SET @commission = @commission + (@grossRevenue * @threscomm)
      END
      ELSE
      BEGIN
            IF @grossRevenue <= (@upper-@lower)
              SET @commission = @commission + (@grossRevenue * @threscomm)
            ELSE
              SET @commission = @commission + ((@upper-@lower) * @threscomm)

            SET @grossRevenue = @grossRevenue - (@upper-@lower)
      END

      FETCH NEXT FROM c_Tier INTO @lower, @upper, @threscomm
END

CLOSE c_Tier
DEALLOCATE c_Tier

RETURN @commission

END

GO

SELECT dbo.calculateCommission(300500,1)

Scott Pletcher

I'm also trying to work something up ... will post ASAP.

tmyint

ASKER

The SQL posted by <b>cmilleruk</b> is wrong.

If you try SELECT dbo.calculateCommission(90000,1) you would expect 45000.00 returned, as the person would be at the 50% tier.

The function returns the negative number of -56500.00.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tmyint

ASKER

To ScottPletcher:

Thanks, but this is also incorrect.

Although the SELECT statement returns correct values for the two sample user records above (for users 1111111 and 2222222), wrong values are returned for other users (IDs 6 and 7):

INSERT INTO revenues VALUES(6, 120000, 0)
INSERT INTO revenues VALUES(7, 265000, 0)

UserID 6 should show commissions of 61,000. The above SELECT returns 60,000.
UserID 7 should show commissions of 141,500. The above SELECT returns 132,500.

Scott Pletcher

Actually, if the flag is 0, the commission is always 50%. Please try with a type of 1.

tmyint

ASKER

thanks - my error. Looks good.