This stored procedure returns the top5 most purchased items in the Adventure Works Cycles application. It is the engine behind the "Most popular items" user control.
Definition:CREATE Procedure usp_ProductsMostPopular AS SELECT TOP 5 SOD.ProductID, SUM(SOD.OrderQty) as TotalNum, P.[Name] FROM Sales.SalesOrderDetail AS SOD INNER JOIN Production.Product AS P ON SOD.ProductID=P.ProductID WHERE SOD.SalesOrderID IN (SELECT TOP 1000 SO.SalesOrderID From Sales.SalesOrderHeader AS SO ORDER BY SO.OrderDate DESC) GROUP BY SOD.ProductID, P.[Name] ORDER BY TotalNum DESC;Database Tables Used:
Product: The Product table contains the core information about all of the items for sale on the Adventure Works Cycles web site. Its primary key is the ProductID identity field.
SalesOrderDetail: The SalesOrderDetail table contains detailed product information for each order in the system. For each product in an order, an entry is made into the SalesOrderDetail table containing the ProductID, Quantity and current UnitCost of the item. There is a many to one relationship between SalesOrderDetail table and the SalesOrderHeader table. Note the primary key in the table is both SalesOrderID and LineNumber (since those two columns are the minimum required to guarantee uniqueness of a record).