title: SQL finding average value of n rows where n is a sum of a field
body: <p>I have data that looks like this.</p>
  
  <p>SoldToRetailer</p>
  
  <pre><code>OrderDate  | Customer  | Product  | price | units
  -------------------------------------------------
  1-jun-2011 | customer1 | Product1 | $10   | 5
  2-jun-2011 | customer1 | Product1 | $5    | 3
  3-jun-2011 | customer1 | Product2 | $10   | 4
  4-jun-2011 | customer1 | Product1 | $4    | 4
  5-jun-2011 | customer2 | Product3 | $10   | 1
  </code></pre>
  
  <p>SalesByRetailers</p>
  
  <pre><code>Customer  | Product  | units
  -----------------------------
  customer1 | Product1 | 5
  customer2 | Product3 | 1
  </code></pre>
  
  <p>Here's what I need.</p>
  
  <p>Sales(average price)</p>
  
  <pre><code>Customer  | Product  | units | Average Price
  --------------------------------------------
  customer1 | Product1 | 5     | $3.44
  customer2 | Product3 | 1     | $10
  </code></pre>
  
  <p>Average Price is defined as the average price of the most recent SoldToRetailer Prices that add up to the units.</p>
  
  <p>So in the first case, I grab the orders from June 4th and June 2nd.  I don't need (actually want) the orders from june 1st to be included.</p>
  
  <p>EDIT:  Hopefully a better explanation.  </p>
  
  <p>I'm attempting to determine the correct (most recent) price where an item was sold to a retailer. It's LIFO order for the prices. The price is determined by averaging the price sold over the last n orders.  Where n = total retail sales for a particular product and customer.</p>
  
  <p>In SQL pseudcode it would look like this.</p>
  
  <pre><code>Select s1.Customer, s1.product, average(s2.price)
  from SalesByRetailers s1
  join SoldToRetailer s2
  on s1.customer=s2.customer
  and s1.product=s2.product
  and ( select top (count of records where s2.units = s1.units) from s2 order by OrderDate desc)
  </code></pre>
  
  <p>What I need to return is the number of records from SoldToRetailer where the sum of units is >= SalesByRetailer Units.</p>
  
  <p>It looks like it could be solved by a RANK or rowover partition, but I'm at a loss.</p>
  
  <p>The SoldToRetailer table is ginormous so performance is at a premium.</p>
  
  <p>Running on SQL 2008R2
  Thanks for helping</p>