CODEWITHSUNDEEP
sql-serverfunctiont-sqlsubquery
mdcg
mdcg

Reputation: 65

Compare sales for the current month with the previous month - Functions - T-SQL

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

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

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

McNets
McNets

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

Related Questions