Hi,
If you have historical data, like:
Received DTS ItemID Qty Amount
2001.01.01 1010 10 $10.00
2001.01.02 1010 10 $11.00
2001.01.04 1011 8 $34.00
2001.01.05 1010 5 $9.75
2001.01.08 1010 10 $10.50
and
Sales DTS ItemID Qty
2001.01.03 1010 12
2001.01.04 1010 5
2001.01.08 1011 1
2001.01.09 1010 10
where DTS is the DateTimeStamp of the operation, and Amount is the unit price (cost).
For the 2001.01.09 sale, we know we have sell 12+5 units of 1010 (1011 is irrelevant for 2001.01.09 sale) so, looking back to received, we know we are somewhere within the 2001.01.02 reception (since it is the smallest date for which SUM(qty) is > 12+5). Selling 10 more units of 1010, that will bring us up to the 2001.01.08 reception ( the smallest date for which SUM(qty of 1010 items) >= 12+5, already sold, +10, actually sold ).
From there, we know the cost will be 3 units (what is left of what we received the 2001.01.02) at 11.00$, 5 units at 9.75$ ( 2001.01.05 reception) and 2 units (what is left to make 10 units, which will complete the 2001.01.09 sale) at 10.50$ (2001.01.08 reception).
A possible SQL statement would look complex. As example, for the 2001.01.09 sale:
SELECT 17 AS alreadySold,
27 As totalQty,
r.dts,
LAST(r.qty) as limit,
Nz(SUM(rn.qty), 0) As msum,
limit+ msum as ul,
iif(ul> alreadySold, ul-alreadySold, 0) As mmax,
iif(mmax> LAST(r.qty), Last(r.qty), mmax) As mmin,
totalQty-msum as momin,
iif(momin>mmin, mmin, momin) As theqty,
LAST(r.Amount) As theAmount,
theqty*LAST(r.Amount) As thecost
FROM Received as r LEFT JOIN Received As rn ON r.ItemID = rn.ItemID AND r.dts>rn.dts
WHERE r.itemID=1010
GROUP BY r.dts;
produces
Query1 alreadySold totalQty dts limit msum ul mmax mmin momin theqty theAmount thecost
17 27 2001.01.01 10 0 10 0 0 27 0 $10.00 0
17 27 2001.01.02 10 10 20 3 3 17 3 $11.00 33
17 27 2001.01.05 5 20 25 8 5 7 5 $9.75 48.75
17 27 2001.01.08 10 25 35 18 10 2 2 $10.50 21
and you have to DSUM("theCost", "Query1") to get your answer. Note that we still have some magical constant, namely 17 and 27, which are the total qty already sell, and the total qty sell after the 2001.01.09 sale.
(intermediate results: limit = qty of the considered reception; msum = SUM(qty) from previous receptions; ul=SUM(qty) up to and including the actual reception; mmax is a limit that zero-out qty already sold; mmin assures us we won't exceed the actual amount received; monim assures us we won't consider more than the sale; theqty is the min of the previous 3 quantities, the number of items at the theAmount cost to be considered in the final cost)
Hoping it may help,
Vanderghast, Access MVP
