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).
The previous example successfully showed an outer join query which produced a list of customers who hadn't placed any orders so far this year.
If the sales manager wanted to produce a list of sales staff who hadn't taken an order in a particular date range, then this could be achieved by running a query that fetches a list of all sales staff names and excludes data found in a sub-query that returns all sales transactions in the set date range.
The results below are from a query that lists all staff names and contains a sub-query that specifies that the staff_id's found in this data are NOT IN the records in the Transactions table in the trans_staff_id field, where the transaction date is in the range of 01/01/2017 and 31/01/2017:
| staff_fullname |
|---|
| Mr Ernie Ecclestone |
| Mr Harry Harrison |
MySQL Code
SELECT CONCAT(s.staff_salutation,' ',s.staff_firstname,' ',s.staff_surname) AS staff_fullname
FROM staff AS s
WHERE s.staff_id NOT IN (
SELECT trans_staff_id
FROM transactions
WHERE (trans_date BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59')
)
T-SQL (MS SQL)
SELECT s.staff_salutation + ' ' + s.staff_firstname + ' ' + s.staff_surname AS staff_fullname
FROM staff AS s
WHERE s.staff_id NOT IN (
SELECT trans_staff_id
FROM transactions
WHERE (trans_date BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59')
)
A query that excludes data can be used to produce a list of companies who bought widgets in January, but have not purchased since. This information would be useful in a sales environment, as a phone call to these customers could generate extra business.
Here a query is produced that lists companies with transactions in the period 01/01/2017 to 31/01/2017, but excludes a sub-query that generates purchases made since. This leaves companies that have purchased in January, but not since.
| cust_id | c.cust_name |
|---|---|
| 4568 | Billingham's of Braithwaite Ltd |
| 4573 | Gimley of Gosborton Ltd |
| 4584 | Rena's Supplies Ltd |
| 4587 | United Uniforms Ltd |
| 4589 | Wilton's Fine Products Ltd |
MySQL Code
SELECT c.cust_id, c.cust_name
FROM customer AS c
INNER JOIN transactions AS t
ON t.trans_cust_id = c.cust_id
WHERE (t.trans_date BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59') AND c.cust_id NOT IN (
SELECT cust_id
FROM customer AS c
INNER JOIN transactions AS t
ON t.trans_cust_id = c.cust_id
WHERE (t.trans_date BETWEEN '2017-02-01 00:00:00' AND '2017-12-31 23:59:59')
) GROUP BY c.cust_id
T-SQL (MS SQL)
SELECT c.cust_id, c.cust_name
FROM customer AS c
INNER JOIN transactions AS t
ON t.trans_cust_id = c.cust_id
WHERE (t.trans_date BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59') AND c.cust_id NOT IN (
SELECT cust_id
FROM customer AS c
INNER JOIN transactions AS t
ON t.trans_cust_id = c.cust_id
WHERE (t.trans_date BETWEEN '2017-02-01 00:00:00' AND '2017-12-31 23:59:59')
) GROUP BY c.cust_id, c.cust_name
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.