SQL finding average value of n rows where n is a sum of a field

I have data that looks like this.

SoldToRetailer

OrderDate  | Customer  | Produc开发者_如何学Got  | 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

SalesByRetailers

Customer  | Product  | units
-----------------------------
customer1 | Product1 | 5
customer2 | Product3 | 1

Here's what I need.

Sales(average price)

Customer  | Product  | units | Average Price
--------------------------------------------
customer1 | Product1 | 5     | $3.44
customer2 | Product3 | 1     | $10

Average Price is defined as the average price of the most recent SoldToRetailer Prices that add up to the units.

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.

EDIT: Hopefully a better explanation.

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.

In SQL pseudcode it would look like this.

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)

What I need to return is the number of records from SoldToRetailer where the sum of units is >= SalesByRetailer Units.

It looks like it could be solved by a RANK or rowover partition, but I'm at a loss.

The SoldToRetailer table is ginormous so performance is at a premium.

Running on SQL 2008R2 Thanks for helping

---

So I used 3 techniques. First I created a table with an over by clause to give me a sorted list of products and prices, then I edited the table to add in the running average. An OUTER APPLY sub select fixded my final problem. Hopefully the code will help someone else with a similar problem.

A shout out to Jeff Moden of SQLSderverCentral.com fame for the running average help.

     SELECT d.ProductKey,
         d.ActualDollars,
         d.Units,
         ROW_NUMBER() OVER(PARTITION BY ProductKey ORDER BY d.OrderDateKey DESC) AS RowNumber,
         NULL                                                                  AS RunningTotal,
         CONVERT(DECIMAL(10, 4), 0)                                            AS RunningDollarsSum,
         CONVERT(DECIMAL(10, 4), 0)                                            AS RunningAverage
  INTO   #CustomerOrdersDetails
  FROM   dbo.orders d
  WHERE  customerKey = @CustomerToSelect

  --DB EDIT...  Google "Quirky update SQL Server central.  Jeff Moden's version of a
  --Running total.  Holy crap it's faster.  tried trangular joins before. 
  CREATE CLUSTERED INDEX [Index1]
    ON #CustomerOrdersDetails ( ProductKey ASC, RowNumber ASC )

  DECLARE @RunningTotal INT
  DECLARE @PrevProductKey INT
  DECLARE @RunningDollarsSum DECIMAL(10, 4)

  UPDATE #CustomerOrdersDetails
  SET    @RunningTotal = RunningTotal = CASE
                                          WHEN ProductKey = @PrevProductKey THEN c.Units + ISNULL(@RunningTotal, 0)
                                          ELSE c.Units
                                        END,
         @RunningDollarsSum = RunningDollarsSum = CASE
                                                    WHEN ProductKey = @PrevProductKey THEN c.ActualDollars + ISNULL(@RunningDollarsSum, 0)
                                                    ELSE c.ActualDollars
                                                  END,
         @PrevProductKey = ProductKey,
         RunningAverage = @RunningDollarsSum / NULLIF(@RunningTotal, 0)
  FROM   #CustomerOrdersDetails c WITH (TABLOCKX)
  OPTION (MAXDOP 1)

  -- =============================================
  -- Update Cost fields with average price calculation
  -- =============================================
  UPDATE d
  SET    DolSoldCostUSD = COALESCE(d.DolSoldCostUSD,
                                   d.UnitsSold * a.RunningAverage),
  FROM   dbo.inbound d
         OUTER APPLY (SELECT TOP 1 *
                      FROM   #CustomerOrdersDetails ap
                      WHERE  ap.ProductKey = d.ProductKey
                             AND d.UnitsSold + d.UnitsOnHand + d.UnitsOnOrder + d.UnitsReceived + d.UnitsReturned >= RunningTotal
                      ORDER  BY RunningTotal) AS a

---

declare @table table (customer varchar(15), product varchar(15), qty int, price decimal(6,2))

insert into @table (customer, product, qty, price)
values
('customer1', 'product1', 5, 3),
('customer1', 'product1', 4, 4),
('customer1', 'product1', 3, 2),
('customer1', 'product1', 2, 13),
('customer1', 'product1', 3, 3),

('customer1', 'product2', 5, 1),
('customer1', 'product2', 4, 7),
('customer1', 'product2', 2, 5),
('customer1', 'product2', 6, 23),
('customer1', 'product2', 2, 1),

('customer2', 'product1', 2, 1),
('customer2', 'product1', 4, 4),
('customer2', 'product1', 7, 3),
('customer2', 'product1', 1, 12),
('customer2', 'product1', 2, 3),

('customer2', 'product2', 3, 2),
('customer2', 'product2', 6, 5),
('customer2', 'product2', 8, 4),
('customer2', 'product2', 2, 11),
('customer2', 'product2', 1, 2)

select customer, product, sum(qty) as units, (sum(qty * price))/SUM(qty) as 'Average Price' from @table 
group by customer, product