>Yes, have them installed. We want vendor names along with the product names and the quantity, yes?
>
>Take a look at the URL I included...it links to a screen shot of the result set, if you run for the parameters I provided.

Haven't thought about optimization too much - so this is straight forward solution

ALTER PROCEDURE [dbo].[GetTopNVendors_TopNProducts] 

@TopVendorCount int =5 , 
@TopProductCount int =3, 
@StartDate date ='01-01-2002',
@EndDate date = '12-31-2004' 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
-- read 3 tables from AdventureWorks - Purchasing.PurchaseOrderHeader, 
-- Purchasing.PurchaseOrderDetail,and Production.Product
--- Retrieve the top X vendors based on $$$ (PurchaseOrderHeader.TotalDue)
--- For each of the top X vendors, retrieve the top Y products based on $$$ (PurchaseOrderDetail.LineTotal)
--- The proc should accept four parameters: the topcount number of vendors, 
--  topcount number of products, and a start/end date range (for the order date)
--- use Dense Ranking

;with cte_TopVendors as (select * from (
select *, DENSE_RANK() over (order by [TotalDue] DESC) as Rank from 
(SELECT OH.[PurchaseOrderID],   
      OH.VendorID, 
      V.Name as [Vendor], SUM(OH.[TotalDue]) over (PARTITION by OH.VendorID) as [TotalDue]   
  FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader] OH 
       inner join Purchasing.Vendor V on OH.VendorID = V.VendorID 
       where OrderDate between @StartDate and @EndDate ) TV) Y where Rank <= @TopVendorCount),
       
  AllProducts as (SELECT c.*, POD.ProductID,  SUM([LineTotal]) over (partition by c.VendorID, 
  POD.ProductID) as ProductTotalDue, P.Name as [Product]     
  FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail] POD 
  inner join cte_TopVendors c on POD.[PurchaseOrderID] = c.[PurchaseOrderID]
  inner join Production.Product P on POD.ProductID = P.ProductID),
  
  AllProductsRanked as (select *, 
  DENSE_RANK() over (PARTITION by VendorID order by ProductTotalDue DESC) as ProductRank from AllProducts)

select distinct Vendor, TotalDue, Rank, Product, ProductTotalDue, ProductRank  
from AllProductsRanked where ProductRank  <= @TopProductCount
order by Rank, ProductRank 


 end   


GO
exec [dbo].[GetTopNVendors_TopNProducts] @TopVendorCount =5 , @TopProductCount =3, 
@StartDate ='01-01-2002', @EndDate = '12-31-2004'