So, I'm in need of some help with some homework.
Below is the schema of the parts of the database that I need to use.
I'm sure that there are some redundancies in it (I would appreciate any help with the setup as well). Anyways, here is the assignment:
The sales manager would now like you to create a report that ranks her products by both their total sales and total sales quantity (each will be its own column). Create a stored procedure that returns
the following columns but also with the two new rank columns added.Product Name | Orders Count | Total Sales Value | Total Sales Quantity
The CEO would like to know the sales based on last month's sales. Please provide her with a query to rank the products by the number of orders in the last month. There should be NO SKIPPED NUMBERS.
Create a SELECT statement that generates a list of orders with customer id, order date, etc. ***Use the row number function to generate a unique id for each row that is sorted by order date DESC
Try it
CREATE PROCEDURE report_salesmanager
AS
WITH cte_main AS
(
SELECT p.NAME,
Isnull(Count(DISTINCT o.orderid), 0) corderid,
Sum(Isnull(o.ordertotal, 0)) sordertotal,
Sum (Isnull(oi.orderitemquantity, 0)) sorderitemquantity
FROM product p
LEFT JOIN orderitem oi
INNER JOIN orders o
ON o.orderid - oi.orderid
ON p.productid = oi.productid
GROUP BY p.NAME), cte_rank_value AS
(
SELECT NAME,
Rank() OVER( ORDER BY sordertotal DESC) rk
FROM cte_main), cte_rank_qtd AS
(
SELECT NAME,
Rank() OVER( ORDER BY sorderitemquantity DESC) rk
FROM cte_main)
SELECT a.NAME [Product Name],
a.corderid,
[Orders Count] a.sordertotal [Total Sales Value],
b.rk [Rank Total Sales Value],
a.sorderitemquantity [Total Sales Quantity],
c.rk [Rank Total Sales Quantity]
FROM cte_main a
INNER JOIN cte_rank_value b
ON a.NAME = b.NAME
INNER JOIN cte_rank_qtd c
ON a.NAME = c.NAME
GO
CREATE PROCEDURE report_ceo
AS
DECLARE @somedateLastMonth DATE = Dateadd(-1,m,Getdate()) WITH cte_main AS
(
SELECT p.NAME,
Isnull(Count(DISTINCT o.orderid), 0) corderid,
Sum(Isnull(o.ordertotal, 0)) sordertotal,
Sum (Isnull(oi.orderitemquantity, 0)) sorderitemquantity
FROM product p
INNER JOIN orderitem oi
INNER JOIN orders o
ON o.orderid - oi.orderid
ON p.productid = oi.productid
WHERE Year(o.orderdate) * 100 + Month(o.orderdate) = Year(@somedateLastMonth) * 100 + Month(@somedateLastMonth)
GROUP BY p.NAME),
cte_rank_value AS
(
SELECT NAME,
Rank() OVER( ORDER BY sordertotal DESC) rk
FROM cte_main),
cte_rank_qtd AS
(
SELECT NAME,
Rank() OVER( ORDER BY sorderitemquantity DESC) rk
FROM cte_main)
SELECT a.NAME [Product Name],
a.corderid,
[Orders Count] a.sordertotal [Total Sales Value],
b.rk [Rank Total Sales Value],
a.sorderitemquantity [Total Sales Quantity],
c.rk [Rank Total Sales Quantity]
FROM cte_main a
INNER JOIN cte_rank_value b
ON a.NAME = b.NAME
INNER JOIN cte_rank_qtd c
ON a.NAME = c.NAME
go
SELECT Row_number() OVER (ORDER BY o.orderdate DESC, o.ordertime DESC) rowNumber ,
*
FROM product p
INNER JOIN orderitem oi
INNER JOIN orders o
ON o.orderid - oi.orderid
ON p.productid = oi.productid
INNER JOIN cart c
ON c.orderid = o.orderid