Kevin,

Just an example how to do this in SQL 2005 without CTE's

SELECT  c.VendorID, v.Name, c.PurchaseOrderID AS POID, c.OrderDate, c.TotalDue ,c.Total 
FROM(SELECT RANK() OVER (PARTITION BY VendorID ORDER BY OrderDate ,PurchaseOrderId) as rn, *
	FROM (SELECT *,
		(SELECT SUM(P.totalDue) FROM Purchasing.PurchaseOrderHeader P 
			WHERE P.OrderDate BETWEEN '20060101' AND U.OrderDate AND U.VendorID = P.VendorID) AS Total 
		FROM Purchasing.PurchaseOrderHeader U
		WHERE U.OrderDate BETWEEN '20060101' AND '20061231') as x
	WHERE total > 10000) as c
	INNER JOIN Purchasing.Vendor V ON c.VendorID = V.BusinessEntityID 
WHERE rn = 1
ORDER BY c.VendorID 

Walter,





>See the attached image, a partial screen shot for the final answer
>
>Using the AdventureWorks database (and specifically the tables Purchasing.PurchaseOrderHeader and Purchasing.Vendor), we want to write a query that will do the following:
>
>- For orders in 2006, show the first order for each Vendor that gave them a cumulative order $$$ amount of at least $10,000 for the year (of 2006)
>
>- So in the attached image, order 161 for Vendor 1494 for $9,776.2665 gave them a cumulative amount in 2006 of at least $10,000.
>
>- Not all vendors will show. Vendor 1492 does not show because they did not accumulate $10,000 in orders in 2006
>
>From PurchaseOrderHeader, you'll want to use the columns VendorID, PurchaseOrderID, OrderDate, and TotalDue (the dollar amount on the order)
>From the Vendor master, you'll want to use the BusinessEntityID (which joins to the VendorID in the POHeader table) and the Vendor Name
>
>A hint....the PO Header table has an Order Date column. You could have two orders on the same day, and perhaps either the first or the second would put the vendor over the magic $10,000 value. For purposes of this example, assume that the lower value for the PurchaseOrderID column is the first of the two orders.
>
>I have a query that produces the result, but curious if anyone wants to give it a shot. My solution is probably not the most optimal, so I'll be happy if someone has a solution that's more efficient.