OK, here's another one... sorry, I don't have prizes to give out :(

Write a stored procedure that will do the following:

- 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

Keep in mind that the top Y products for Vendor A could be a completely different set of products than the top Y products for Vendor B.

If you want to test, you could call your stored proc, like so:

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

And here's the result set I get with AdventureWorks (with the version of ADW that I have)
www.commongroundsolutions.net/VendorOutput.jpg (mea maxima culpa, I didn't format the currency output)

And a few ground rules

1) Test code before sending :)
(2) This is for SQL 2005 or SQL 2008 <s>

I did it with 2 CTEs, a table-valued UDF, and a CROSS APPLY - but there's usually several ways to skin the proverbial cat.