Finding Most Popular Sold Item Using Stored Procedure

3910 views sql-server
4

This is my first time posting to this site. I'm trying to use a stored procedure in MSSQL Server Management Studio to find the most popular item sold in a given month and year. What I have so far is this:

CREATE PROCEDURE dbo.sp_GetMonthlyProductPopularity (@popularItemX INTEGER = NULL)
AS
  BEGIN
      SELECT *
      FROM   (SELECT DATEPART(mm, soh.OrderDate)   AS [Month],
                     DATEPART(yyyy, soh.OrderDate) AS [Year],
                     pr.[Name]                     AS 'Product Name',
                     SUM(sod.ProductID)            AS 'Quantity Sold',
                     Rank()
                       OVER (
                         Partition BY SUM(sod.ProductID)
                         ORDER BY soh.OrderDate)   AS [Rank]
              FROM   Production.Product AS pr
                     JOIN Sales.SalesOrderDetail sod
                       ON ( pr.ProductID = sod.ProductID )
                     JOIN Sales.SalesOrderHeader soh
                       ON ( sod.SalesOrderID = soh.SalesOrderID )
              GROUP  BY soh.OrderDate,
                        pr.[Name]) a
      WHERE  a.[Rank] < 2
      ORDER  BY a.[Year],
                a.[Month]
  END

Go 

It executes correctly, but it ranks all items as Rank 1 and so it shows them all. I can't figure out what I am doing wrong here. Any help is well appreciated to get me on the right track. Thanks!

answered question

By the way, I am using the AdventureWorks 2012 Database given out by Microsoft for this problem.

1 Answer

2

I think the issue is on Partition BY SUM(sod.ProductID)

You can try to use a subquery to get SUM by pr.[Name] and yaer, then make row number for the price.

SELECT *
FROM
(
    SELECT *,Rank() OVER (Partition BY [Product Name] ORDER BY [Quantity Sold] desc)   AS [Rank] 
    FROM(
        SELECT DATEPART(mm, soh.OrderDate)   AS [Month],
             DATEPART(yyyy, soh.OrderDate) AS [Year],
             pr.[Name]                     AS 'Product Name',
             SUM(sod.ProductID)            AS 'Quantity Sold'
        FROM   Production.Product AS pr
             JOIN Sales.SalesOrderDetail sod
               ON ( pr.ProductID = sod.ProductID )
             JOIN Sales.SalesOrderHeader soh
               ON ( sod.SalesOrderID = soh.SalesOrderID )
        GROUP  BY 
            DATEPART(mm, soh.OrderDate), 
            DATEPART(yyyy, soh.OrderDate),
                    pr.[Name]
    ) a
) a
WHERE  a.[Rank] < 2
ORDER  BY a.[Year],
        a.[Month]

posted this

Have an answer?

JD

Please login first before posting an answer.