Jump to content
TapeGun007

Auto Insert based upon value

Recommended Posts

Posted (edited)

Is it better or are you able to auto insert a percentage based upon a value.

Example:

Tech1 sells a $1,000 referral item and gets 5% commission

Tech1 sells a $2,500 referral item and gets 7% commission instead.

I know I could code this in php no problem.  I'm just wondering if this makes sense or if it's possible just to auto insert either 5%  for under $2,500 or 7% if the sale is equal or over $2,500.  There is only 5% and 7%, no other options.  The sale amount is stored in the same table.

Edited by TapeGun007

Share this post


Link to post
Share on other sites

For something that simple: yes, in MySQL 5.7+ you can do it using generated columns and a CASE expression, however if the logic ever needs to change then you have to do an ALTER TABLE.

The fact that you would have to alter the table definition for something so simple and arbitrary means you should not do it. The commission rate should be determined through code, then stored with the rest of the sale data.

Share this post


Link to post
Share on other sites

Create a commission rate table with each rate and the range of values to which it applies

testsale                 testrate
+----+---------+         +----+------------+----------+------+
| id | amount  |         | id | value_from | value_to | rate |
+----+---------+         +----+------------+----------+------+
|  1 | 1000.00 |         |  1 |       1.00 |  2499.99 | 0.05 |
|  2 | 2000.00 |         |  2 |    2500.00 | 99999.99 | 0.07 |
|  3 | 2500.00 |         +----+------------+----------+------+
|  4 | 3000.00 |
+----+---------+

SELECT s.id
    , s.amount
    , r.rate
    , ROUND(s.amount * r.rate, 2) as commission
FROM testsale s
    JOIN testrate r ON s.amount BETWEEN r.value_from AND r.value_to;
    
+----+---------+------+------------+
| id | amount  | rate | commission |
+----+---------+------+------------+
|  1 | 1000.00 | 0.05 |      50.00 |
|  2 | 2000.00 | 0.05 |     100.00 |
|  3 | 2500.00 | 0.07 |     175.00 |
|  4 | 3000.00 | 0.07 |     210.00 |
+----+---------+------+------------+

The same principle can also be applied to dates, so the correct rate is applied when querying historical data

testsale                              testrate
+----+---------+------------+         +----+------------+----------+------+------------+-------------+
| id | amount  | date_sold  |         | id | value_from | value_to | rate | valid_from | valid_until |
+----+---------+------------+         +----+------------+----------+------+------------+-------------+
|  1 | 1000.00 | 2019-02-26 |         |  1 |       1.00 |  2499.99 | 0.05 | 2001-01-01 | 9999-12-31  |
|  2 | 2000.00 | 2019-02-27 |         |  2 |    2500.00 | 99999.99 | 0.07 | 2001-01-01 | 2019-02-28  |
|  3 | 2500.00 | 2019-02-28 |         |  3 |    2500.00 | 99999.99 | 0.08 | 2019-03-01 | 9999-12-31  |
|  4 | 3000.00 | 2019-03-01 |         +----+------------+----------+------+------------+-------------+
+----+---------+------------+

SELECT s.id
    , date_sold
    , s.amount
    , r.rate
    , ROUND(s.amount * r.rate, 2) as commission
FROM testsale s
    JOIN testrate r ON s.amount BETWEEN r.value_from AND r.value_to
                    AND s.date_sold BETWEEN r.valid_from AND r.valid_until
    
+----+------------+---------+------+------------+
| id | date_sold  | amount  | rate | commission |
+----+------------+---------+------+------------+
|  1 | 2019-02-26 | 1000.00 | 0.05 |      50.00 |
|  2 | 2019-02-27 | 2000.00 | 0.05 |     100.00 |
|  3 | 2019-02-28 | 2500.00 | 0.07 |     175.00 |
|  4 | 2019-03-01 | 3000.00 | 0.08 |     240.00 |
+----+------------+---------+------+------------+

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.