BYU Student Author: @Andrew
Reviewers: @DylanKing, @Jonathan_Weston
Estimated Time to Solve: 45 Minutes
We provide the solution to this challenge using:
- SQL (Azure Data Studio)
Need a program? Click here.
This is an add-on to Challenge 69|Bike Shop Shipping Delays.
Overview
You are an accountant for a small bicycle company. Your company owns and operates three different locations. Recently, you received an email from the company’s VP of Sales requesting a schedule of sales revenue for each salesperson. Instead of simply building a dataset and exporting it to excel, you need to build a function that can be integrated with a reporting system. This will allow the VP of Sales to access the report on their own instead of requesting it from you.
They have specifically requested a report including the following:
- A table displaying salesperson & their store then revenue by month for a particular year.
- The report is called from a SQL function for a given year, so report delivery can easily be automated and accessible to the VP of Sales.
Instructions
First, follow the instructions below to install Azure Data Studio and then load the database. If you have already installed Azure Data Studio and setup a local instance of Microsoft SQL Server, then skip to loading the database. If you have already loaded the database from Challenge 69|Bike Shop Shipping Delays, then skip to the next instructions.
Azure Data Studio Installation
-
Install Microsoft SQL Server (if not already installed) found here. Download the developer version (seen in the image below) and install package. When prompted select the basic installation package.
-
Install Azure Data Studio (if not already installed). Installation instructions can be found here.
-
Open Azure Data Studio and set up a new local server instance (if not already done):
Database Loading Instructions
- Download the database setup file: Challenge73_Database.sql.
- Open it in Azure Data Studio, make sure the selected server is localhost, and run the file. Running the file will create a database named BikeStores, then create tables and insert data into those tables.
For more information on the database structure, tables, and fields see SQLServerTutorial.net’s data dictionary here.
This database, ERD, and data dictionary are all owned by SQLServerTutorial.net and used within this challenge under their terms of use.
Second, review the entity-relationship diagram for the BikeStores database. The ERD will be useful in writing your queries.
Third, setup a new query (CTRL + N shortcut) to complete your data retrieval to build the function to create the sales report. Additional instructions on your deliverable:
-
Create a function in the sales schema named ‘sales_report’ that will allow the user to pass a parameter for the desired year of the report to the function then output a table of the sales report.
Function Format
The query to call the function is where ‘2017’ is the desired year for the report:
SELECT * FROM sales.sales_report(2017) -
The ‘sales_report’ function should output 14 columns
- Store, where store is the store name the salesperson works at
- Salesperson, where salesperson is the name of the staff that made the sale
- Jan - Dec, where each of the 12 months is a field containing the sales revenue generated by a salesperson for that particular month. The date reflected is the order date. Sales revenue from a particular order item is calculated as (List Price - Discount) * Quantity. HINT: to effectively create this table, use the PIVOT() function.
Detailed Instructions
First, focus on writing a select statement with the correct output before creating the function. The necessary select statement is nested.
To write the first select statement follow these steps:
- Write a select statement with the columns:
- Store name
- Salesperson (first & last name)
- Order month
- (List Price - Discount) * Quantity as item revenue
- This select statement should contain a where clause that limits the report to a single year.
- If you need additional guidance, see the code below:
--INITIAL SELECT
SELECT
sto.store_name AS 'Store',
sta.first_name + ' ' + sta.last_name AS 'Salesperson',
MONTH(order_date) AS 'order_month',
(soi.list_price-soi.discount)*soi.quantity AS 'revenue'
FROM
sales.orders so
JOIN sales.order_items soi ON so.order_id = soi.order_id
JOIN sales.staffs sta ON so.staff_id = sta.staff_id
JOIN sales.stores sto ON so.store_id = sto.store_id
WHERE YEAR(order_date) = 2017
To write the final nested SELECT statement follow these steps:
- The SELECT statement you wrote in the previous steps is the table to select from in this next part. Wrap it in parentheses and give it an alias.
- Write the SELECT portion of the statement. Understanding the columns we will output, start with Store name and Salesperson. Since we haven’t written the pivot function yet, skip the month fields for now.
- After the FROM statement, write the PIVOT function. For additional help see the code below:
--PIVOT FUNCTION
PIVOT(
SUM(revenue)
FOR order_month IN
([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS pvt
- Add the month fields to the SELECT statement referenced the way they are referenced in the PIVOT() function.
Once you have created a successful SELECT statement that produces the sales report, write the create function statement and replace the placeholder year in the where clause to the function parameter.
Call the function you just made for 2017 and compare the output table to the solution table below.
Solution
Solution Code
CREATE FUNCTION sales.sales_report (@order_year int)
RETURNS TABLE
AS
RETURN(
SELECT
Store,
Salesperson,
[1] AS 'Jan',
[2] AS 'Feb',
[3] AS 'Mar',
[4] AS 'Apr',
[5] AS 'May',
[6] AS 'Jun',
[7] AS 'Jul',
[8] AS 'Aug',
[9] AS 'Sep',
[10] AS 'Oct',
[11] AS 'Nov',
[12] AS 'Dec'
FROM
(SELECT
sto.store_name AS 'Store',
sta.first_name + ' ' + sta.last_name AS 'Salesperson',
MONTH(order_date) AS 'order_month',
(soi.list_price-soi.discount)*soi.quantity AS 'revenue'
FROM
sales.orders so
JOIN sales.order_items soi ON so.order_id = soi.order_id
JOIN sales.staffs sta ON so.staff_id = sta.staff_id
JOIN sales.stores sto ON so.store_id = sto.store_id
WHERE YEAR(order_date) = @order_year
) ord
PIVOT(
SUM(revenue)
FOR order_month IN
([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS pvt
);
Solution Video: Challenge 73|SQL – Bike Shop Sales Report