The purpose of this set of web pages is to demonstrate knowledge of building Microsoft Excel based solutions, data structure design and live querying on data tables (MySQL and MS SQL).
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:
| staff_fullname | trans_grn_widgets_sales | trans_blu_widgets_sales | widget_sales | bonus |
|---|---|---|---|---|
| Anthea Allen | 5000.00 | 6000.00 | 11000.00 | 200 |
| Billy Beaumont | 6200.00 | 8800.00 | 15000.00 | 300 |
| Charles Chuzzlewit | 11800.00 | 3000.00 | 14800.00 | 300 |
| Deirdrie Dawson | 10500.00 | 9200.00 | 19700.00 | 300 |
| Francesca Faversham | 9000.00 | 8400.00 | 17400.00 | 300 |
| Gina Ginola | 7500.00 | 6600.00 | 14100.00 | 300 |
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
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 ASC
The T-SQL code equivalent is:
T-SQL (MS SQL)
SELECT g.staff_fullname, g.trans_grn_widgets_sales,
g.trans_blu_widgets_sales, g.widget_sales,
CASE
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
ELSE 0
END AS Bonus
FROM
(
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
Below are the results as per the above query, but the date range specified is February 2017:
| staff_fullname | trans_grn_widgets_sales | trans_blu_widgets_sales | widget_sales | bonus |
|---|---|---|---|---|
| Anthea Allen | 7000.00 | 9400.00 | 16400.00 | 300 |
| Billy Beaumont | 10000.00 | 13750.00 | 23750.00 | 300 |
| Charles Chuzzlewit | 9500.00 | 16800.00 | 26300.00 | 300 |
| Deirdrie Dawson | 4000.00 | 6600.00 | 10600.00 | 100 |
| Ernie Ecclestone | 5000.00 | 9600.00 | 14600.00 | 300 |
| Francesca Faversham | 5500.00 | 5400.00 | 10900.00 | 100 |
| Gina Ginola | 4500.00 | 6600.00 | 11100.00 | 200 |
| Harry Harrison | 5000.00 | 14400.00 | 19400.00 | 300 |
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.
| staff_fullname | trans_grn_widgets_sales | trans_blu_widgets_sales | widget_sales | bonus |
|---|---|---|---|---|
| Anthea Allen | 1500.00 | 1800.00 | 3300.00 | 0 |
| Charles Chuzzlewit | 1500.00 | 1800.00 | 3300.00 | 0 |
| Deirdrie Dawson | 3500.00 | 3600.00 | 7100.00 | 0 |
| Ernie Ecclestone | 4500.00 | 6600.00 | 11100.00 | 200 |
| Francesca Faversham | 2500.00 | 4800.00 | 7300.00 | 0 |
| Gina Ginola | 2500.00 | 4800.00 | 7300.00 | 0 |
| Harry Harrison | 2500.00 | 4800.00 | 7300.00 | 0 |
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.