troubleshooting Question

dense rank and Grouping

Avatar of Leo Torres
Leo TorresFlag for United States of America asked on
SQL
12 Comments1 Solution402 ViewsLast Modified:
Code below does not give me totals correctly this works on AdventureWorks2008R2 Database

Data should look like Picture


Select VendorName,VendorRank,TotalDue,ProductName,ProductRank,ProductTotalDue  from (
Select *, DENSE_RANK() OVER (ORDER BY TotalDue DESC) VendorRank
		, DENSE_RANK() OVER (ORDER BY ProductTotalDue DESC) ProductRank from ( 
Select	v.Name as VendorName
		,p.Name as ProductName
		,SUM(ph.TotalDue) over(partition BY ph.VendorID) as TotalDue
		,SUM(ph.TotalDue) over(partition BY pd.ProductID) as ProductTotalDue
from Purchasing.PurchaseOrderHeader ph (nolock)
	inner Join Purchasing.PurchaseOrderDetail pd (nolock)
		on ph.PurchaseOrderID = pd.PurchaseOrderDetailID and ph.OrderDate between '2007-01-01' and '2008-06-30'
	inner join Production.Product p (nolock)
		on p.ProductID = pd.ProductID	
	Inner Join Purchasing.Vendor v (nolock)
		on v.BusinessEntityID = ph.VendorID	
Group By VendorID,pd.ProductID,v.Name,p.Name, ph.TotalDue
)  a
) b
Where VendorRank <= 5 and ProductRank <= 5
Order by TotalDue desc
EE-Rank3.png
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros