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.
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.
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.
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.
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.
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 (, , , , , , , , , , , ) ) 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.
CREATE FUNCTION sales.sales_report (@order_year int) RETURNS TABLE AS RETURN( SELECT Store, Salesperson,  AS 'Jan',  AS 'Feb',  AS 'Mar',  AS 'Apr',  AS 'May',  AS 'Jun',  AS 'Jul',  AS 'Aug',  AS 'Sep',  AS 'Oct',  AS 'Nov',  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 (, , , , , , , , , , , ) ) AS pvt );
Solution Video: Challenge 73|SQL – Bike Shop Sales Report