Should this SP return multiple result sets or one result (top Y of products)?
Also, how the order date comes into play?
>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.
If it's not broken, fix it until it is.
My Blog