http://www.elsevierdirect.com/companion.jsp?ISBN=9780123820228 chapter 27 - partitioning data page 491

lifo_fifo.sql

CREATE TABLE InvRec(
  rcpt_nr integer primary key,
  purchase_date datetime not null,
  qty_on_hand integer not null
    check (qty_on_hand>= 0),
  unit_price decimal(12,4) not null
);


insert into InvRec values(1,'2011-01-01',15,10.00);
insert into InvRec values(2,'2011-01-02',25,12.00);
insert into InvRec values(3,'2011-01-03',40,13.00);
insert into InvRec values(4,'2011-01-04',35,12.00);
insert into InvRec values(5,'2011-01-05',45,10.00);


create view lifo as
  select
    r1.purchase_date stock_date,
    r1.unit_price unit_price,
    sum(r2.qty_on_hand) tot_qty_on_hand,
    sum(r2.qty_on_hand*r2.unit_price) tot_cost
  from
    invRec as r1, invrec as r2
  where r2.purchase_date >= r1.purchase_date
  group by r1.purchase_date,r1.unit_price;


SELECT (tot_cost - ((tot_qty_on_hand -:order_qty_on_hand) * unit_price)) AS cost
FROM LIFO AS L1
WHERE stock_date = 
  (SELECT MAX(stock_date) FROM LIFO AS L2 WHERE tot_qty_on_hand >=:order_qty_on_hand);

SELECT SUM(R3.v) AS cost
FROM (SELECT R1.unit_price *
CASE WHEN SUM(R2.qty_on_hand) <=:order_qty_on_hand
  THEN R1.qty_on_hand
  ELSE:order_qty_on_hand - (SUM(R2.qty_on_hand) - R1.qty_on_hand)
END
FROM InventoryReceipts AS R1, InventoryReceipts AS R2
WHERE R1.purchase_date <= R2.purchase_date
GROUP BY R1.purchase_date, R1.qty_on_hand, R1.unit_price
HAVING (SUM(R2.qty_on_hand) - R1.qty_on_hand) <=:order_qty_on_hand) AS R3(v);


CREATE VIEW FIFO AS
SELECT
  R1.purchase_date stock_date,
  R1.unit_price unit_price,
  SUM(R2.qty_on_hand) tot_qty_on_hand,
  SUM(R2.qty_on_hand * R2.unit_price) tot_cost
FROM InventoryReceipts AS R1, InventoryReceipts AS R2
WHERE R2.purchase_date <= R1.purchase_date
GROUP BY R1.purchase_date, R1.unit_price;


SELECT (tot_cost - ((tot_qty_on_hand -:order_qty_on_hand) * unit_price)) AS cost
FROM FIFO AS F1
WHERE stock_date =
  (SELECT MIN (stock_date) FROM FIFO AS F2 WHERE tot_qty_on_hand >= :order_qty_on_hand);

Extracted code from

Joe Celko's SQL for Smarties, 4th Edition

Advanced SQL Programming
By Joe Celko

If you think I am violating copyright, please let me know. I will take required actions to comply with.