How to Calculate Cost of Goods Sold

Question

I have an inventory table. Inventory table SQL:

CREATE TABLE INVENTORY
(
    INVENTORY_ID             SERIAL PRIMARY KEY,
    INVENTORY_DATE           date NOT NULL,
    ITEM_NAME                text NOT NULL,
    PURCHASED_QUANTITY       INTEGER DEFAULT 0,
    SOLD_QUANTITY            INTEGER DEFAULT 0,
    AMOUNT                   MONEY NOT NULL,
    TOTAL                    MONEY NOT NULL       
);

INSERT INTO INVENTORY(INVENTORY_DATE, ITEM_NAME, PURCHASED_QUANTITY, SOLD_QUANTITY, AMOUNT, TOTAL)
SELECT '1/1/2014'::date, 'ITEM-001', 10, NULL, 100, 1000 UNION ALL
SELECT '1/2/2014'::date, 'ITEM-001', NULL, 2, 200, 400 UNION ALL
SELECT '1/3/2014'::date, 'ITEM-001', 20, NULL, 110, 2200 UNION ALL
SELECT '1/4/2014'::date, 'ITEM-001', NULL, 4, 200, 800 UNION ALL
SELECT '1/5/2014'::date, 'ITEM-001', 20, NULL, 80, 1600;

Table

SELECT * FROM INVENTORY;

INVENTORY_DATE    ITEM_NAME    PURCHASED_QUANTITY    SOLD QUANTITY     AMOUNT    TOTAL
-----------------------------------------------------------------------------------------
1/1/2014          ITEM-001     10                                      $100      $1000
1/2/2014          ITEM-001                           2                 $200      $400
1/3/2014          ITEM-001     20                                      $110      $2200
1/4/2014          ITEM-001                           4                 $200      $800
1/5/2014          ITEM-001     20                                      $80       $1600

I am trying to create a function like this:

CREATE FUNCTION GET_FIFO_COGS(ITEM_NAME TEXT, QUANTITY INTEGER)
RETURNS MONEY
AS
$$
BEGIN

END
$$
LANGUAGE "PLPGSQL";

So that, I could :

SELECT * FROM GET_FIFO_COGS('ITEM-001', 4) --> $400
--Remaining 4. Each for $100.

SELECT * FROM GET_FIFO_COGS('ITEM-001', 12) --> $1280
--4 for $100, 8 for 110.

SELECT * FROM GET_FIFO_COGS('ITEM-001', 34) --> $3400
--4 for $100, 20 for $110, 10 for $80.

I can improve this question if pointed into the right direction. I am learning SQL, this one is giving me troubles and I am unable to solve this problem because I find it quite tricky for me.

Answer 1

SQL Fiddle

The approach in this solution is to expand each row with n items to n rows with a single item using generate_series. Then building two separate sets for purchased and sold. Each set's rows are numbered in the inventory_id order so it is possible to eliminate the purchased items that have already been sold. Then order by the inventory_id and limit to the quantity.

create or replace function get_fifo_cogs(_item_name text, _quantity int)
returns money as $$
    with inventory as (
        select 
            inventory_id, inventory_date,
            generate_series(1, greatest(purchased_quantity, sold_quantity)),
            amount,
            purchased_quantity is not null as purchased
        from inventory
        where item_name = _item_name
    ), purchased as (
        select *, row_number() over(order by inventory_id) as rn
        from inventory
        where purchased
    ), sold as (
        select *, row_number() over(order by inventory_id) as rn
        from inventory
        where not purchased
    )
    select sum(amount) as cogs
    from (
        select amount
        from purchased
        where not exists (
            select 1
            from sold
            where rn = purchased.rn
        )
        order by inventory_id
        limit _quantity
    ) s;
$$ language sql;

Use it as

select cogs from get_fifo_cogs('ITEM-001', 4) gfc(cogs);
  cogs   
---------
 $400.00
(1 row)

select cogs from get_fifo_cogs('ITEM-001', 12) gfc(cogs);
   cogs    
-----------
 $1,280.00
(1 row)

select cogs from get_fifo_cogs('ITEM-001', 34) gfc(cogs);
   cogs    
-----------
 $3,400.00

Or just

select get_fifo_cogs('ITEM-001', 4) as cogs;