>>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.
>
>SQL 2012 makes it quite easy:
>
>

>;
>
>WITH cte
>AS (
>	SELECT *
>		,sum(P.TotalDue) OVER (
>			PARTITION BY P.VendorID ORDER BY P.OrderDate ROWS UNBOUNDED PRECEDING
>			) AS CumulativeTotal
>	FROM Purchasing.PurchaseOrderHeader P
>	WHERE P.OrderDate >='20060101' and P.OrderDate < '20070101'
>	)
>	,cte1
>AS (
>	SELECT *
>		,ROW_NUMBER() OVER (
>			PARTITION BY VendorID ORDER BY OrderDate
>				,PurchaseOrderId
>			) AS Rn
>	FROM cte
>	WHERE CumulativeTotal >= 10000
>	)
>SELECT DISTINCT c.VendorID
>	,V.NAME
>	,c.PurchaseOrderID AS POID
>	,c.OrderDate
>	,c.TotalDue
>	,c.CumulativeTotal
>FROM cte1 c
>INNER JOIN Purchasing.vVendorWithContacts V ON c.VendorID = V.BusinessEntityID
>WHERE Rn = 1 ORDER BY VendorID

Very nice.