Nested IF Statements

Bonus Calculation for January 2017

Here the query calculates the staff bonus for each staff member, based on the widget sales for January 2017.

A single rate bonus is calculated as follows:

If the staff member has brought in sales of £12,000 or above, £300 is paid
Sales of £11,000 or above, but less than £12,000 earns £200
Making sales of £8000 or above, but less than £11,000 earns £100
No bonus is paid for widget sales of less than £8,000

The date has been formatted, staff name is concatenated and additional calculations have been made for widget_sales.

A date range has been applied to pick up only January 2017 widget sales:

Anthea Allen5000.006000.0011000.00200
Billy Beaumont6200.008800.0015000.00300
Charles Chuzzlewit11800.003000.0014800.00300
Deirdrie Dawson10500.009200.0019700.00300
Francesca Faversham9000.008400.0017400.00300
Gina Ginola7500.006600.0014100.00300

The MySQL code used is as follows:

MySQL Code

SELECT CONCAT(s.staff_firstname, ' ',s.staff_surname) AS staff_fullname, SUM(t.trans_grn_widgets_sales), SUM(t.trans_blu_widgets_sales),
SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) AS widget_sales,
IF(SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) >= 12000, '300',
 IF(SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) >= 11000,'200',
  IF(SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) >= 8000,'100', '0'))) AS bonus
FROM transactions AS t
ON t.trans_staff_id = s.staff_id
WHERE (trans_date BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59')
GROUP BY s.staff_id ASC

The T-SQL code equivalent is:


SELECT g.staff_fullname, g.trans_grn_widgets_sales,
g.trans_blu_widgets_sales, g.widget_sales,
  WHEN g.widget_sales >= 12000 THEN 300
  WHEN g.widget_sales < 12000 AND g.widget_sales >= 11000 THEN 200
  WHEN g.widget_sales <11000 AND g.widget_sales >= 8000 THEN 100
END AS Bonus
  SELECT s.staff_firstname + ' ' + s.staff_surname AS staff_fullname,
  SUM(t.trans_grn_widgets_sales) AS trans_grn_widgets_sales,
  SUM(t.trans_blu_widgets_sales) trans_blu_widgets_sales,
  SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) AS widget_sales
  FROM transactions AS t
  INNER JOIN staff AS s
  ON t.trans_staff_id = s.staff_id
  WHERE (trans_date BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59')
  GROUP BY s.staff_id, s.staff_surname, s.staff_firstname
) AS g

Bonus Calculation for February 2017

Below are the results as per the above query, but the date range specified is February 2017:

Anthea Allen7000.009400.0016400.00300
Billy Beaumont10000.0013750.0023750.00300
Charles Chuzzlewit9500.0016800.0026300.00300
Deirdrie Dawson4000.006600.0010600.00100
Ernie Ecclestone5000.009600.0014600.00300
Francesca Faversham5500.005400.0010900.00100
Gina Ginola4500.006600.0011100.00200
Harry Harrison5000.0014400.0019400.00300

Bonus Calculation for March 2017

Below are the results as per the above query, but the date range specified is March 2017.

It should be noted that the original dataset supplied only contains data up until mid-March. Therefore many staff will have not made sales sufficient for a bonus to be earned.

Anthea Allen1500.001800.003300.000
Charles Chuzzlewit1500.001800.003300.000
Deirdrie Dawson3500.003600.007100.000
Ernie Ecclestone4500.006600.0011100.00200
Francesca Faversham2500.004800.007300.000
Gina Ginola2500.004800.007300.000
Harry Harrison2500.004800.007300.000

Click the button below to advance to the next stage

Next: >> Try it Out! - Staff

Or use the above 'SQL Queries' dropdown menu to jump to any stage.