Reputation: 65
I have created a SalesMonth function that receives as parameter a month number and a year number and presents a totaled list with the products sold in the month and year passed as parameter. The list shows the following product information: Identifier, Month, Year, Total Sold Quantity, Average Unit Price, Total Discount Granted and Total Value Sold.
Follow the code below:
CREATE FUNCTION SalesMonth (@month int, @year int)
RETURNS TABLE
AS
RETURN (
SELECT
AdventureWorks.Sales.SalesOrderDetail.ProductID,
MONTH(AdventureWorks.Sales.SalesOrderHeader.OrderDate) as 'Mes',
YEAR(AdventureWorks.Sales.SalesOrderHeader.OrderDate) as 'Ano',
SUM(AdventureWorks.Sales.SalesOrderDetail.OrderQty) as 'Quantidade Total Vendida',
AVG(AdventureWorks.Sales.SalesOrderDetail.UnitPrice) as 'Preco Unitario Médio',
SUM(AdventureWorks.Sales.SalesOrderDetail.UnitPriceDiscount) as 'Desconto Total',
SUM(AdventureWorks.Sales.SalesOrderDetail.LineTotal) as 'Valor Total Vendido'
FROM
AdventureWorks.Sales.SalesOrderDetail
INNER JOIN
AdventureWorks.Sales.SalesOrderHeader
ON
AdventureWorks.Sales.SalesOrderHeader.SalesOrderID = AdventureWorks.Sales.SalesOrderDetail.SalesOrderID
WHERE
MONTH(AdventureWorks.Sales.SalesOrderHeader.OrderDate) = @month
AND
YEAR(AdventureWorks.Sales.SalesOrderHeader.OrderDate) = @year
GROUP BY
AdventureWorks.Sales.SalesOrderDetail.ProductID,
MONTH(AdventureWorks.Sales.SalesOrderHeader.OrderDate),
YEAR(AdventureWorks.Sales.SalesOrderHeader.OrderDate)
)
Now I need to compare which items had a growth in sales compared to the previous month. I thought about doing something like:
SELECT *
FROM SalesMonth(10, 2001)
WHERE SumPrice > (SELECT SumPrice FROM SalesMonth(9, 2001))
I know this is a gross error, but I could not think of anything like it. Would anyone have any idea of similar query for this problem?
Upvotes: 0
Views: 2037
Reputation: 13969
You can try accessing by joining as below:
SELECT a.*, b.*, Case When a.[Desconto Total] >= b.[Desconto Total] then 'Greater than or equal to previous Month' else 'Less than previous month' End as [Comparison]
FROM SalesMonth(10, 2001) a
left join SalesMonth(9,2001) b
on a.ProductId = b.ProductId
Upvotes: 0
Reputation: 10827
Join sales of month 10 with sales on month 9.
SELECT *
FROM SalesMonth(10, 2001) M10
INNER JOIN SalesMonth(9, 2001) M9
ON M10.ProductId = M9.ProductID
WHERE M10.SumPrice > M9.SumPrice;
Upvotes: 1