SQL amount consumed query?

How do you do a query to calculate how much of an item has been consumed (used up)?

We can find the qty of each item that we purchased in a purchases table with columns Id, ProductId, Qty (decimal), Date

Id, ProductId, Qty, Date    
1,  1,         10,  1/1/11
2,  1,        开发者_高级运维 5,   2/2/11
3,  1,         8,   3/3/11

And how do you then add a count of how many of each row in the purchase table have been consumed - assuming strict FIFO? So in the above example if we know that 14 have been consumed the output would be:

Id, ProductId, Qty, Date,    Consumed    
1,  1,         10,  1/1/11,  10
2,  1,         5,   2/2/11,  4
3,  1,         8,   3/3/11,  0

Hopefully that explains what I mean by an amount consumed query - we know 14 were consumed and that the first purchase was for 10, so all 10 have been consumed. The next purchase was for 5 so we know that 4 of those have been consumed.

Theres two places I can get the consumed data from - the ConsumedItems table: columns Id, ProductId, QtyUsed, Date), or from the ConsumedSummaryView with columns ProductId, QtyUsed (this is the sum of ConsumedItems.QtyUsed)

Sample table and view

create table purchases (Id int, ProductId int, Qty int, Date datetime)
insert purchases select 1,  1,         10,  '1/1/11'
insert purchases select 2,  1,         5,   '2/2/11'
insert purchases select 3,  1,         8,   '3/3/11'
create view ConsumedSummaryView as select ProductID = 1, QtyUsed = 14

The query

;with p as (
    select *, rn=ROW_NUMBER() over (partition by productid order by date, id)
    from purchases)
, tmp(Id, ProductId, Qty, Date, rn, ToGo, Consumed) as (
    select p.Id, p.ProductId, p.Qty, p.Date, cast(1 as bigint),
            CAST(ISNULL(v.qtyused,0) - p.Qty as decimal(20,10)),
        cast(case
            when v.qtyused >= p.Qty Then p.Qty
            when v.qtyused > 0 then v.qtyused
            else 0 end as decimal(20,10))
    from p
    left join ConsumedSummaryView v on p.ProductId = v.productId
    where rn=1
    union all
    select p.Id, p.ProductId, p.Qty, p.Date, cast(p.rn as bigint),
            cast(ISNULL(tmp.toGo,0) - p.Qty as decimal(20,10)),
        cast(case
            when tmp.toGo >= p.Qty Then p.Qty
            when tmp.toGo > 0 then tmp.toGo
            else 0 end as decimal(20,10))
    from tmp
    --inner join p on p.rn=tmp.rn+1
    inner join p on p.rn=tmp.rn+1 and p.productid = tmp.ProductId
)
select Id, ProductId, Qty, Date, Consumed
from tmp
order by rn

Output

Id          ProductId   Qty         Date                    Consumed
----------- ----------- ----------- ----------------------- -----------
1           1           10          2011-01-01 00:00:00.000 10
2           1           5           2011-02-02 00:00:00.000 4
3           1           8           2011-03-03 00:00:00.000 0

A little different approach than Richard's, but I'm not sure which will perform better:

SELECT
    Purchases.Id,
    Purchases.ProductId,
    Purchases.Qty,
    Purchases.Date, 
    CASE
        WHEN COALESCE (PreviousPurchases.PreviousUsed, 0) + Qty < ConsumedSummaryView.QtyUsed THEN Qty
        ELSE
            CASE
                WHEN ConsumedSummaryView.QtyUsed - COALESCE (PreviousPurchases.PreviousUsed, 0) < 0 THEN 0 
                ELSE ConsumedSummaryView.QtyUsed - COALESCE (PreviousPurchases.PreviousUsed, 0)
            END
    END AS Used
FROM
    Purchases
    INNER JOIN ConsumedSummaryView ON Purchases.ProductId = ConsumedSummaryView.ProductId
    LEFT OUTER JOIN (
        SELECT
            SUM(Purchases_2.Qty) AS PreviousUsed,
            Purchases_1.Id
        FROM
            Purchases AS Purchases_2
            INNER JOIN Purchases AS Purchases_1 ON Purchases_2.Id < Purchases_1.Id
                                               AND Purchases_2.ProductId = Purchases_1.ProductId
        GROUP BY
            Purchases_1.Id
    ) AS PreviousPurchases ON Purchases.Id = PreviousPurchases.Id