Postgresql - Count sales price from purchases prices FIFO

ezulex
August 4, 2024
171 views
0 votes
2 Answers

Given:
2 tables: sales and purchases

Sales: product_id, count, date
Purchases: product_id, purchase number, count of items in the purchase, cost per unit

Task:
To calculate the cost of goods sold using the FIFO (first in – first out, first in – first out) model. Deliveries are ranked from older to newer by increasing delivery number. Sales write off the cost of goods first from the first shipment, when it runs out from the second shipment, and so on.

What I’ve tried:
Sorted the sales by date and id and made windows cumulative sum of the number of sales. But here is how to link to a specific purchase and move to the next one, in case the quantity from the purchase is over I can not understand.

SELECT
    "Product ID" AS product_id,
    "Date" AS date,
    "Sales QTY" AS sales_qty,
    SUM("Sales QTY") OVER (PARTITION BY "Product ID"
        ORDER BY "Date" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cummulative_sale_sum
FROM "SALES"
ORDER BY product_id, date

I used purchase cumulatives and subtraction, but as a result I get wrong amounts since the 2nd month.

WITH sales_grouped AS (
    SELECT
        "Product ID",
        SUM("Sales QTY") AS total_sales_qty,
        to_char("Date", 'YYYY-MM') AS year_month
    FROM "SALES"
    GROUP BY "Product ID", to_char("Date", 'YYYY-MM')
),
supply_cumulative AS (
    SELECT
        "Product ID",
        "Supply QTY",
        "#Supply",
        "Costs Per PCS",
        SUM("Supply QTY") OVER (PARTITION BY "Product ID" ORDER BY "#Supply") AS cumulative_qty
    FROM "SUPPLY"
),
matched_sales AS (
    SELECT
        s."Product ID",
        s.year_month,
        s.total_sales_qty,
        c."Supply QTY",
        c."Costs Per PCS",
        c.cumulative_qty - c."Supply QTY" AS prev_cumulative_qty
    FROM sales_grouped s
    JOIN supply_cumulative c
    ON s."Product ID" = c."Product ID"
),
fifo_calculation AS (
    SELECT
        "Product ID",
        year_month,
        "Costs Per PCS",
        CASE
            WHEN total_sales_qty > prev_cumulative_qty THEN
                LEAST(total_sales_qty - prev_cumulative_qty, "Supply QTY")
            ELSE 0
        END AS used_qty
    FROM matched_sales
)
SELECT
    "Product ID",
    year_month,
    SUM(used_qty * "Costs Per PCS") AS total_cost
FROM fifo_calculation
GROUP BY "Product ID", year_month
ORDER BY "Product ID", year_month;

Tags: postgresql sql

Answers

To actual match sales with purchases in the order of purchases. Your best bet is

you should sort Purchases by product_id and purchase number to get them in the order they were made.
Then Sales by product_id and date to get them in the order they were sold.
Match Sales with Purchases using a cumulative sum approach to track how many items have been sold and deplete the corresponding purchase orders

You can use this code snippet:

WITH sales AS (
    SELECT
        product_id,
        count AS sales_qty,
        date
    FROM sales_table
),
purchases AS (
    SELECT
        product_id,
        purchase_number,
        count AS purchase_qty,
        cost_per_unit
    FROM purchases_table
),
sorted_sales AS (
    SELECT
        product_id,
        sales_qty,
        date,
        SUM(sales_qty) OVER (PARTITION BY product_id ORDER BY date) AS cumulative_sales
    FROM sales
),
sorted_purchases AS (
    SELECT
        product_id,
        purchase_number,
        purchase_qty,
        cost_per_unit,
        SUM(purchase_qty) OVER (PARTITION BY product_id ORDER BY purchase_number) AS cumulative_purchases
    FROM purchases
),
fifo_matching AS (
    SELECT
        s.product_id,
        s.date,
        s.sales_qty,
        s.cumulative_sales,
        p.purchase_number,
        p.purchase_qty,
        p.cost_per_unit,
        p.cumulative_purchases
    FROM sorted_sales s
    JOIN sorted_purchases p
    ON s.product_id = p.product_id
    WHERE s.cumulative_sales <= p.cumulative_purchases
    OR s.cumulative_sales - s.sales_qty < p.cumulative_purchases
)
SELECT
    product_id,
    date,
    SUM(LEAST(sales_qty, purchase_qty - (cumulative_sales - sales_qty)) * cost_per_unit) AS cogs
FROM fifo_matching
GROUP BY product_id, date
ORDER BY product_id, date;

- Atmo
- August 4, 2024 at 6:58 pm
- 0 votes
0
Your question takes a little bit of methodology. There are 3 parts in it
1. Build the cumulative quantities of items purchased and sold.
2. Join the records together.
3. Calculate a quantity sold for the joined records.
For reasons that will become clear later, I will address the points in order 1, 3 and finally 2.

Cumulative quantities

This is the easy part: it consists in calculating cumulative sums for each product; for example:
```
SUM(count) OVER (PARTITION BY product_id ORDER BY date)
```
Calculating the quantities

For the sake of the explanation, let’s assume we are able to correctly join the purchases (in green below) and sales (in red) together. The quantity to output depends on the purchase/sold counts as well as the stock and total sold on the joined record.

A visual representation is going to be helpful. Each record falls into one of 4 configurations:

This must be read as:
- Leftmost case: the sale is entirely covered by the current purchase.
  This includes the situation where segments have the same end(s).
- 2^nd case: part of the sale is covered by the previous purchase; the rest of the sale is covered by the current purchase.
- 3^rd case: part of the sale is covered by the current purchase; the rest is covered by the next purchase.
- Rightmost case: Mix of 2 and 3.
Respective to the above description, the quantity to be returned can be calculated as one of:
1. Sale count
2. total_sold - previous(stock)
3. stock - previous(total_sold)
4. Purchase count
The right quantity can be chosen with a CASE WHEN ... ELSE ... but it is very verbose. Alternatively, a simple LEAST should have the same effect.

Joining purchases and sales

The JOIN follows the very same rules as above, but this time consists in looking at all the records at once, like in the below fashion:

This must be read as:
- Sale #1 (by date) must be joined with purchase #1 (by id).
- Sale #2 must be joined with purchases #1 and #2.
- Sale #3 must be joined with purchase #2.
- Sale #4 must be joined with purchases #2 and #3.
- etc…
I have not covered the 4^th case from above in that visual representation but anyhow, it should make it slightly easier to write the correct join condition:
```
previous(total_sold) BETWEEN previous(stock) AND stock - 1`
OR total_sold BETWEEN previous(stock) + 1 AND stock
OR stock BETWEEN previous(total_sold) + 1 AND total_sold
```
SQL

For the resulting SQL, it must be noted there were discrepancies in the column names from your description and your code, with spaces in both sides. I therefore went with my own names, without spaces.

As to decrease the number of CTEs, the previous(stock) and previous(total_sold) from above must respectively be replaced by (stock - s.count) (s being the alias for the stock CTE) and (total_sold - ts.count) ts being the alias for the total_sale CTE):
```
WITH stock(id, product_id, count, unit_cost, stock) AS (
    SELECT *, SUM(count) OVER (PARTITION BY product_id ORDER BY id) 
    FROM purchase
), total_sale(product_id, count, date, total_sold) AS (
    SELECT *, SUM(count) OVER (PARTITION BY product_id ORDER BY date)
    FROM sale
)
SELECT date, ts.product_id, unit_cost,
LEAST(ts.count, total_sold - (stock - s.count), stock - (total_sold - ts.count), s.count) AS Qty
FROM stock s
LEFT JOIN total_sale ts 
    ON s.product_id = ts.product_id 
    AND ((total_sold - ts.count) BETWEEN (stock - s.count) AND stock - 1
         OR ts.total_sold BETWEEN (stock - s.count) + 1 AND stock
         OR stock BETWEEN (total_sold - ts.count) + 1 AND total_sold
        )
ORDER BY s.product_id, id, date
```
Note that you may want/need to change the type of join for a INNER JOIN if you want to only keep sales (the LEFT JOIN keeps purchase #4 from my above visual representation).
You can also use a RIGHT JOIN to see if, for any reason, there are more sales than purchases (it is not supposed to be possible).
Login or Signup to reply.