Mysql - Tracking inventory and purchase price of products over time

biimix
January 15, 2024
129 views
0 votes
2 Answers

I’m trying to calculate daily inventory availability for different products using MadiaDB.

First of all, there is a products table containing data for every product that is being sold.
I’m getting sales data to product_sales_date table on daily level for several products from API. Sales might not exist for certain date-product combination based on… well… real sales.
Purchase data is filled in manually to product_purchase_date table by users, data exists for certain dates as well, based on real purchase orders.

The problem is that purchase order might be delivered to the warehouse much before the inventory leftover becomes zero. So even there is a new in-delivery, there is available stock from previous deliveries existing.

My aim is to calculate inventory, sales and purchase price for every day based on the First In First Out (FIFO) concept. With that idea every new in-delivery shall not be considered until the old one is sold out. Only then I need to start using the new purchase prices to calculate profit.

Hope that is clear, otherwise please ask questions for further details.

In simplified way Sales data I’m receiving from api looks like this

product_sale_date

id	product_name	date_sale	qty_sale	price_sale
1	product1	2023-12-01	100	10.70
2	product2	2023-12-01	80	15.00
3	product1	2023-12-02	50	11.00
4	product2	2023-12-02	60	15.20
5	product1	2023-12-03	70	9.90
6	product1	2023-12-04	65	10.80
7	product1	2023-12-05	50	10.80
8	product1	2023-12-07	75	10.70
9	product2	2023-12-07	35	14.50
10	product1	2023-12-10	50	11.00
11	product2	2023-12-10	60	15.10
12	product1	2023-12-11	70	9.80
13	product2	2023-12-11	95	14.30
14	product1	2023-12-12	65	10.10
15	product2	2023-12-12	40	14.90

and then manually purchase orders are registered like that

product_purchase_date

id	product_name	date_purchase	qty_purchase	price_purchase
1	product1	2023-12-01	300	6.30
2	product2	2023-12-01	250	7.40
3	product1	2023-12-04	200	6.00
4	product2	2023-12-04	300	7.20
5	product1	2023-12-10	200	7.00
6	product2	2023-12-10	200	8.00

Here is the code for filling in this data to temp tables for testing:

DROP TEMPORARY TABLE IF EXISTS product_sale_date;
DROP TEMPORARY TABLE IF EXISTS product_purchase_date;

CREATE TEMPORARY TABLE product_sale_date (id INT, product_name VARCHAR(50), date_sale DATE, qty_sale INT, price_sale DECIMAL(9,2));
CREATE TEMPORARY TABLE product_purchase_date (id INT, product_name VARCHAR(50), date_purchase DATE, qty_purchase INT, price_purchase DECIMAL(9,2));

INSERT INTO product_sale_date SELECT 1, 'product1', '2023-12-01', 100, 10.70;
INSERT INTO product_sale_date SELECT 2, 'product2', '2023-12-01', 80, 15.00;
INSERT INTO product_sale_date SELECT 3, 'product1', '2023-12-02', 50, 11.00;
INSERT INTO product_sale_date SELECT 4, 'product2', '2023-12-02', 60, 15.20;
INSERT INTO product_sale_date SELECT 5, 'product1', '2023-12-03', 70, 9.90;
INSERT INTO product_sale_date SELECT 6, 'product1', '2023-12-04', 65, 10.80;
INSERT INTO product_sale_date SELECT 7, 'product1', '2023-12-05', 50, 10.80;
INSERT INTO product_sale_date SELECT 8, 'product1', '2023-12-07', 75, 10.70;
INSERT INTO product_sale_date SELECT 9, 'product2', '2023-12-07', 35, 14.50;
INSERT INTO product_sale_date SELECT 10, 'product1', '2023-12-10', 50, 11.00;
INSERT INTO product_sale_date SELECT 11, 'product2', '2023-12-10', 60, 15.10;
INSERT INTO product_sale_date SELECT 12, 'product1', '2023-12-11', 70, 9.80;
INSERT INTO product_sale_date SELECT 13, 'product2', '2023-12-11', 95, 14.30;
INSERT INTO product_sale_date SELECT 14, 'product1', '2023-12-12', 65, 10.10;
INSERT INTO product_sale_date SELECT 15, 'product2', '2023-12-12', 40, 14.90;

INSERT INTO product_purchase_date SELECT 1, 'product1', '2023-12-01', 300, 6.30;
INSERT INTO product_purchase_date SELECT 2, 'product2', '2023-12-01', 250, 7.40;
INSERT INTO product_purchase_date SELECT 3, 'product1', '2023-12-04', 200, 6.00;
INSERT INTO product_purchase_date SELECT 4, 'product2', '2023-12-04', 300, 7.20;
INSERT INTO product_purchase_date SELECT 5, 'product1', '2023-12-10', 200, 7.00;
INSERT INTO product_purchase_date SELECT 6, 'product2', '2023-12-10', 200, 8.00;

As a result I expect to get something like that based on this example:

product_name	date	qty_avail	qty_sale	purchase_id	purchase_price
product1	2023-12-01	200	100	1	6.30
product2	2023-12-01	170	80	2	7.40
product1	2023-12-02	150	50	1	6.30
product2	2023-12-02	110	60	2	7.40
product1	2023-12-03	80	70	1	6.30
product2	2023-12-03	110	NULL	2	7.40
product1	2023-12-04	15	65	1	6.30
product2	2023-12-04	110	NULL	2	7.40
product1	2023-12-05	0	15	1	6.30
product1	2023-12-05	165	35	3	6.00
product2	2023-12-05	110	NULL	2	7.40
product1	2023-12-06	165	NULL	3	6.00
product2	2023-12-06	110	NULL	2	7.40
product1	2023-12-07	90	75	3	6.00
product2	2023-12-07	75	35	2	7.40
product1	2023-12-08	90	NULL	3	6.00
product2	2023-12-08	75	NULL	2	7.40
product1	2023-12-09	90	NULL	3	6.00
product2	2023-12-09	75	NULL	2	7.40
product1	2023-12-10	40	50	3	6.00
product2	2023-12-10	15	60	2	7.40
product1	2023-12-11	0	40	3	6.00
product1	2023-12-11	170	30	5	7.00
product2	2023-12-11	0	15	2	7.40
product2	2023-12-11	220	80	4	7.20
product1	2023-12-12	105	65	5	7.00
product2	2023-12-12	180	40	4	7.20

In this example, logic is quite straightforward for the qty_avail calculation at first, it can be calculated with sum of qty_purchase minus sum of qty_sale partitioned by product_name over the date. Then I need to attach to it the id of the purchase order, which in the beginning is obviously the first orders with id=1 of 300 pcs and purchase price = 6.30 for product1 and id=2 of 250 pcs and purchase price = 7.40 for product2. But when at certain moment qty_avail is becoming less 0, I’d need to insert a new row for the same product-date combination:

one row would contain qty_avail = 0, qty_sale = previous date qty_avail, and same purchase_id = previous date purchase_id
and in the new row qty_avail = qty_purchase of the next purchase order id having date less than today, qty_sale = qty_sale – previous date qty_avail, and purchase_id = id of the next purchase order id having date less than today

Also here I would need somehow to make a check if remaining qty_sale for this new row is actually even more than the qty_purchase of the new purchase order, then I would need to have one more row for the same product-date combination and add a next id of the purchase order there. Not sure how can I explain it clearer tbh…

I have an idea how to make it with WHILE loop within stored procedure where I’d be just going through each date and recalculate inventory date and join sales and purchase data based on it but I think there shall be much more elegant solution for this, just cannot really get to it.

Edit #1

I just made it work with a WHILE loop that is not the proper way here I believe but it works and in my example it gives the result I need. However, in case there is a scenario where the purchase order value is less than one time sales, this would not really work. Could you please help me to find out the right way tackling this?
Thank you in advance.

Here is the code of all the preps and stored procedure with WHILE loop

DROP TEMPORARY TABLE IF EXISTS product_sale_date;
DROP TEMPORARY TABLE IF EXISTS product_purchase_date;

CREATE TEMPORARY TABLE product_sale_date (id INT, product_name VARCHAR(50), date_sale DATE, qty_sale INT, price_sale DECIMAL(9,2));
CREATE TEMPORARY TABLE product_purchase_date (id INT, product_name VARCHAR(50), date_purchase DATE, qty_purchase INT, price_purchase DECIMAL(9,2));

INSERT INTO product_sale_date SELECT 1, 'product1', '2023-12-01', 100, 10.70;
INSERT INTO product_sale_date SELECT 2, 'product2', '2023-12-01', 80, 15.00;
INSERT INTO product_sale_date SELECT 3, 'product1', '2023-12-02', 50, 11.00;
INSERT INTO product_sale_date SELECT 4, 'product2', '2023-12-02', 60, 15.20;
INSERT INTO product_sale_date SELECT 5, 'product1', '2023-12-03', 70, 9.90;
INSERT INTO product_sale_date SELECT 6, 'product1', '2023-12-04', 65, 10.80;
INSERT INTO product_sale_date SELECT 7, 'product1', '2023-12-05', 50, 10.80;
INSERT INTO product_sale_date SELECT 8, 'product1', '2023-12-07', 75, 10.70;
INSERT INTO product_sale_date SELECT 9, 'product2', '2023-12-07', 35, 14.50;
INSERT INTO product_sale_date SELECT 10, 'product1', '2023-12-10', 50, 11.00;
INSERT INTO product_sale_date SELECT 11, 'product2', '2023-12-10', 60, 15.10;
INSERT INTO product_sale_date SELECT 12, 'product1', '2023-12-11', 70, 9.80;
INSERT INTO product_sale_date SELECT 13, 'product2', '2023-12-11', 95, 14.30;
INSERT INTO product_sale_date SELECT 14, 'product1', '2023-12-12', 65, 10.10;
INSERT INTO product_sale_date SELECT 15, 'product2', '2023-12-12', 40, 14.90;

INSERT INTO product_purchase_date SELECT 1, 'product1', '2023-12-01', 300, 6.30;
INSERT INTO product_purchase_date SELECT 2, 'product2', '2023-12-01', 250, 7.40;
INSERT INTO product_purchase_date SELECT 3, 'product1', '2023-12-04', 200, 6.00;
INSERT INTO product_purchase_date SELECT 4, 'product2', '2023-12-04', 300, 7.20;
INSERT INTO product_purchase_date SELECT 5, 'product1', '2023-12-10', 200, 7.00;
INSERT INTO product_purchase_date SELECT 6, 'product2', '2023-12-10', 200, 8.00;

DROP TEMPORARY TABLE IF EXISTS purchase;
CREATE TEMPORARY TABLE purchase (id INT, product_name VARCHAR(50), qty_purchase INT);
DROP TEMPORARY TABLE IF EXISTS tbl;
CREATE TEMPORARY TABLE tbl (product_name VARCHAR(50), date DATE, qty_avail INT, qty_sale INT, purchase_id INT);

SET @min_date = '2023-12-01';
SET @max_date = '2023-12-12';
SET @date = @min_date;

DROP PROCEDURE IF EXISTS temp;

DELIMITER $$
CREATE PROCEDURE temp()

BEGIN
    /*run loop until @date reaches last date defined as @max_date*/
    WHILE @date <= @max_date DO
        /*get purchase orders that have been received before @date
        per each product, where id of the purchase order 
        shall be > than the last one saved in the result table*/
        DELETE FROM purchase;
        
        INSERT INTO purchase
        SELECT
            ppd.id
            ,ppd.product_name
            ,ppd.qty_purchase
            ,ppd.price_purchase
        FROM
            product_purchase_date ppd
        LEFT JOIN
            tbl t_prev
        ON
            t_prev.product_name = ppd.product_name
            AND t_prev.date = DATE_ADD(@date, INTERVAL -1 DAY)
        WHERE
            ppd.date_purchase <= @date
            AND IFNULL(t_prev.purchase_id, 0) < ppd.id;

        /*step #1: insert new row to the result table for the
        current @date from product_sale_date table based on
        previous date data from result table*/
        INSERT INTO tbl
        SELECT
            psd.product_name
            ,@date
            ,IFNULL(t_prev.qty_avail, 0) - psd.qty_sale as qty_avail
            ,psd.qty_sale
            ,t_prev.purchase_id
            ,t_prev.price_purchase
        FROM
            product_sale_date psd
        LEFT JOIN
            tbl t_prev
        ON
            t_prev.product_name = psd.product_name
            AND t_prev.date = DATE_ADD(@date, INTERVAL -1 DAY)
            AND t_prev.qty_avail > 0
        WHERE
            psd.date_sale = @date;
        
        /*step #2: update inserted row with purchase information
        from product_purchase_date table in case qty_avail < 0
        (when this day leftover value based on previous 
        day leftover value minus this day sale quantity is
        less than 0, do update and include purchase order data)*/
        UPDATE 
            tbl t
        LEFT JOIN
            product_sale_date psd
        ON
            psd.product_name = t.product_name
            AND psd.date_sale = @date
        LEFT JOIN
            purchase p
        ON
            p.product_name = t.product_name
            AND p.id = (SELECT MIN(id) FROM purchase WHERE product_name = p.product_name)
        LEFT JOIN
            tbl t_prev
        ON
            t_prev.product_name = t.product_name
            AND t_prev.date = DATE_ADD(@date, INTERVAL -1 DAY)
            AND t_prev.qty_avail > 0
        SET
            t.qty_avail = CASE
                            WHEN t_prev.qty_avail is not NULL
                            THEN 0
                            ELSE p.qty_purchase - t.qty_sale
                        END
            ,t.qty_sale = CASE
                            WHEN t_prev.qty_avail is not NULL
                            THEN t_prev.qty_avail
                            ELSE t.qty_sale
                        END
            ,t.purchase_id = CASE
                            WHEN t_prev.qty_avail is not NULL
                            THEN t_prev.purchase_id
                            ELSE p.id
                        END
            ,t.price_purchase = CASE
                            WHEN t_prev.qty_avail is not NULL
                            THEN t_prev.price_purchase
                            ELSE p.price_purchase
                        END
        WHERE
            t.date = @date
            AND t.qty_avail < 0;        
        
        /*step #3: if available quantity of the previous date 
        is less than sold quantity, 
        insert new row with remaining sold quantity and 
        new purchase_id*/
        INSERT INTO
            tbl
        SELECT
            psd.product_name
            ,@date
            ,qty_purchase - (psd.qty_sale - t_prev.qty_avail) as qty_avail
            ,psd.qty_sale - t_prev.qty_avail as qty_sale
            ,p.id as purchase_id
            ,p.price_purchase
        FROM
            product_sale_date psd
        LEFT JOIN
            tbl t_prev
        ON
            t_prev.product_name = psd.product_name
            AND t_prev.date = DATE_ADD(@date, INTERVAL -1 DAY)
        LEFT JOIN
            purchase p
        ON
            p.product_name = psd.product_name
            AND p.id = (SELECT MIN(id) FROM purchase WHERE product_name = p.product_name)
        WHERE
            t_prev.qty_avail - psd.qty_sale < 0
            AND t_prev.qty_avail > 0
            AND psd.date_sale = @date;
            
        /*step #4: oif there is no sales data registered
        for current day, insert new row with previous
        day product-date information and set qty_sale=NULL*/
        INSERT INTO tbl
        SELECT
            t_prev.product_name
            ,@date
            ,t_prev.qty_avail
            ,NULL as qty_sale
            ,t_prev.purchase_id
            ,t_prev.price_purchase
        FROM
            tbl t_prev
        LEFT JOIN
            product_sale_date psd
        ON
            psd.product_name = t_prev.product_name
            AND psd.date_sale = @date
        WHERE
            t_prev.date = DATE_ADD(@date, INTERVAL -1 DAY)
            AND t_prev.qty_avail > 0
            AND psd.id is NULL;
            
        /*step #5: increase @date variable with one day*/
        SET @date = DATE_ADD(@date, INTERVAL 1 DAY);
    END WHILE;
    
    SELECT * FROM tbl ORDER BY 2,1, 3;
END$$
DELIMITER ;

CALL temp();

Tags: mariadb mysql

Answers

There are a bit too many changes of the data within the question to adjust it with the test. So I will show the data I worked with:

DROP TEMPORARY TABLE IF EXISTS product_sale_date;
DROP TEMPORARY TABLE IF EXISTS product_purchase_date;

CREATE TEMPORARY TABLE product_sale_date (id INT, product_name VARCHAR(50), date_sale DATE, qty_sale INT, price_sale DECIMAL(9,2));
CREATE TEMPORARY TABLE product_purchase_date (id INT, product_name VARCHAR(50), date_purchase DATE, qty_purchase INT, price_purchase DECIMAL(9,2));

INSERT INTO product_sale_date SELECT 1, 'product1', '2023-12-01', 100, 10.70;
INSERT INTO product_sale_date SELECT 2, 'product2', '2023-12-01', 80, 15.00;
INSERT INTO product_sale_date SELECT 3, 'product1', '2023-12-02', 50, 11.00;
INSERT INTO product_sale_date SELECT 4, 'product2', '2023-12-02', 60, 15.20;
INSERT INTO product_sale_date SELECT 5, 'product1', '2023-12-03', 70, 9.90;
INSERT INTO product_sale_date SELECT 6, 'product1', '2023-12-04', 65, 10.80;
INSERT INTO product_sale_date SELECT 7, 'product1', '2023-12-05', 50, 10.80;
INSERT INTO product_sale_date SELECT 8, 'product1', '2023-12-07', 75, 10.70;
INSERT INTO product_sale_date SELECT 9, 'product2', '2023-12-07', 35, 14.50;
INSERT INTO product_sale_date SELECT 10, 'product1', '2023-12-10', 50, 11.00;
INSERT INTO product_sale_date SELECT 11, 'product2', '2023-12-10', 60, 15.10;
INSERT INTO product_sale_date SELECT 12, 'product1', '2023-12-11', 70, 9.80;
INSERT INTO product_sale_date SELECT 13, 'product2', '2023-12-11', 95, 14.30;
INSERT INTO product_sale_date SELECT 14, 'product1', '2023-12-12', 65, 10.10;
INSERT INTO product_sale_date SELECT 15, 'product2', '2023-12-12', 40, 14.90;

INSERT INTO product_purchase_date SELECT 1, 'product1', '2023-12-01', 300, 6.30;
INSERT INTO product_purchase_date SELECT 2, 'product2', '2023-12-01', 250, 7.40;
INSERT INTO product_purchase_date SELECT 3, 'product1', '2023-12-04', 200, 6.00;
INSERT INTO product_purchase_date SELECT 4, 'product2', '2023-12-04', 300, 7.20;
INSERT INTO product_purchase_date SELECT 5, 'product1', '2023-12-10', 200, 7.00;
INSERT INTO product_purchase_date SELECT 6, 'product2', '2023-12-10', 200, 8.00;

Not quite sure about the explanation but it seems to me like purchase gets goods in the warehouse and sale gets it out. If so, you can have a series of warehouse events with a cte (events):

WITH events AS
  ( Select    'PURCHASE' as a_type, 
              s.date_purchase as a_date, 
              s.product_name, 
              s.qty_purchase as qty_in,
              0 as qty_out,
              s.price_purchase as price_in,
              0 as price_out,
              Round(s.qty_purchase * s.price_purchase, 2) as amount_in,
              0 as amount_out 
    From      product_purchase_date s
   Union All
    Select    'SALE' as a_type, 
              s.date_sale as a_date, 
              s.product_name, 
              0 as qty_in,
              s.qty_sale as qty_out, 
              0 as price_in,
              s.price_sale as price_out, 
              0 as amount_in,
              Round(s.qty_sale * s.price_sale, 2) as amount_out
    From      product_sale_date s
    Order By  product_name, a_date, a_type
  )

/*  events   R e s u l t :
a_type      a_date      product_name     qty_in     qty_out price_in     price_out  amount_in   amount_out
----------  ----------  ---------------  ------   --------- --------  ------------ ----------  -----------
PURCHASE    2023-12-01  product1            300           0     6.30         0.00     1890.00         0.00
SALE        2023-12-01  product1              0         100     0.00        10.70        0.00      1070.00
SALE        2023-12-02  product1              0          50     0.00        11.00        0.00       550.00
SALE        2023-12-03  product1              0          70     0.00         9.90        0.00       693.00
PURCHASE    2023-12-04  product1            200           0     6.00         0.00     1200.00         0.00
SALE        2023-12-04  product1              0          65     0.00        10.80        0.00       702.00
SALE        2023-12-05  product1              0          50     0.00        10.80        0.00       540.00
SALE        2023-12-07  product1              0          75     0.00        10.70        0.00       802.50
PURCHASE    2023-12-10  product1            200           0     7.00         0.00     1400.00         0.00
SALE        2023-12-10  product1              0          50     0.00        11.00        0.00       550.00
SALE        2023-12-11  product1              0          70     0.00         9.80        0.00       686.00
SALE        2023-12-12  product1              0          65     0.00        10.10        0.00       656.50
PURCHASE    2023-12-01  product2            250           0     7.40         0.00     1850.00         0.00
SALE        2023-12-01  product2              0          80     0.00        15.00        0.00      1200.00
SALE        2023-12-02  product2              0          60     0.00        15.20        0.00       912.00
PURCHASE    2023-12-04  product2            300           0     7.20         0.00     2160.00         0.00
SALE        2023-12-07  product2              0          35     0.00        14.50        0.00       507.50
PURCHASE    2023-12-10  product2            200           0     8.00         0.00     1600.00         0.00
SALE        2023-12-10  product2              0          60     0.00        15.10        0.00       906.00
SALE        2023-12-11  product2              0          95     0.00        14.30        0.00      1358.50
SALE        2023-12-12  product2              0          40     0.00        14.90        0.00       596.00      */

The above resutset could be further processed to get more informative data. Create another cte (grid):

  grid AS
  ( Select a_date, product_name, price_in, qty_in, price_out, qty_out,
           Sum(qty_in - qty_out) 
           Over( Partition By product_name Order By a_date, a_type
                 Rows Between Unbounded Preceding And Current Row ) as qty_stock,
           amount_in as cost, amount_out as revenue,
           Sum(amount_in) 
               Over( Partition By product_name Order By a_date, a_type
                     Rows Between Unbounded Preceding And Current Row ) as total_cost,
           Sum(amount_out) 
               Over( Partition By product_name Order By a_date, a_type
                     Rows Between Unbounded Preceding And Current Row ) as total_revenue
    From events
  )

/*    grid    R e s u l t :
a_date      product_name    price_in   qty_in    price_out  qty_out qty_stock       cost    revenue  total_cost     total_revenue
----------  --------------  --------   ------    ---------  ------- ---------  ---------  ---------  ----------  ----------------
2023-12-01  product1            6.30      300         0.00        0      300     1890.00       0.00     1890.00              0.00
2023-12-01  product1            0.00        0        10.70      100      200        0.00    1070.00     1890.00           1070.00
2023-12-02  product1            0.00        0        11.00       50      150        0.00     550.00     1890.00           1620.00
2023-12-03  product1            0.00        0         9.90       70       80        0.00     693.00     1890.00           2313.00
2023-12-04  product1            6.00      200         0.00        0      280     1200.00       0.00     3090.00           2313.00
2023-12-04  product1            0.00        0        10.80       65      215        0.00     702.00     3090.00           3015.00
2023-12-05  product1            0.00        0        10.80       50      165        0.00     540.00     3090.00           3555.00
2023-12-07  product1            0.00        0        10.70       75       90        0.00     802.50     3090.00           4357.50
2023-12-10  product1            7.00      200         0.00        0      290     1400.00       0.00     4490.00           4357.50
2023-12-10  product1            0.00        0        11.00       50      240        0.00     550.00     4490.00           4907.50
2023-12-11  product1            0.00        0         9.80       70      170        0.00     686.00     4490.00           5593.50
2023-12-12  product1            0.00        0        10.10       65      105        0.00     656.50     4490.00           6250.00
2023-12-01  product2            7.40      250         0.00        0      250     1850.00       0.00     1850.00              0.00
2023-12-01  product2            0.00        0        15.00       80      170        0.00    1200.00     1850.00           1200.00
2023-12-02  product2            0.00        0        15.20       60      110        0.00     912.00     1850.00           2112.00
2023-12-04  product2            7.20      300         0.00        0      410     2160.00       0.00     4010.00           2112.00
2023-12-07  product2            0.00        0        14.50       35      375        0.00     507.50     4010.00           2619.50
2023-12-10  product2            8.00      200         0.00        0      575     1600.00       0.00     5610.00           2619.50
2023-12-10  product2            0.00        0        15.10       60      515        0.00     906.00     5610.00           3525.50
2023-12-11  product2            0.00        0        14.30       95      420        0.00    1358.50     5610.00           4884.00
2023-12-12  product2            0.00        0        14.90       40      380        0.00     596.00     5610.00           5480.00   */

From this resultset different calculations could be derived. Below is calculation of cost, revenue, profit and average purchase price. All the values are running values that include all previous rows with current row included.
Average purchase price is calculated as total amount of purchases so far devided by total quantity purchased so far.
Result is sorted by product for readability…

SELECT g.a_date, g.product_name, 
       Sum(g.qty_in) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) as qty_in, 
       Sum(g.qty_out) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) as qty_out, 
       Sum(g.qty_in - g.qty_out) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) as stock,
       Sum(g.cost) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) as cost, 
       Sum(g.revenue) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) as revenue, 
       --
       Sum(g.revenue) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) - 
       Sum(g.cost) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) as profit,
       --
       Sum(g.cost) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) / 
       Sum(g.qty_in) 
           Over(Partition By g.product_name Order By g.product_name, g.a_date
                Rows Between Unbounded Preceding And Current Row ) as avg_purchase_price
FROM
  (Select    g.a_date, g.product_name,  
            Sum(g.qty_in) as qty_in, Sum(g.qty_out) as qty_out, 
            Max(g.cost) as cost, Max(revenue) as revenue
   From grid g
   Group By g.a_date, g.product_name
   Order By g.product_name, g.a_date
  ) g

/*        R e s u l t : 
a_date      product_name     qty_in qty_out   stock     cost    revenue      profit  avg_purchase_price
----------  ---------------  ------ -------  ------  -------  ---------  ----------  ------------------
2023-12-01  product1            300     100     200  1890.00    1070.00     -820.00            6.300000
2023-12-02  product1            300     150     150  1890.00    1620.00     -270.00            6.300000
2023-12-03  product1            300     220      80  1890.00    2313.00      423.00            6.300000
2023-12-04  product1            500     285     215  3090.00    3015.00      -75.00            6.180000
2023-12-05  product1            500     335     165  3090.00    3555.00      465.00            6.180000
2023-12-07  product1            500     410      90  3090.00    4357.50     1267.50            6.180000
2023-12-10  product1            700     460     240  4490.00    4907.50      417.50            6.414286
2023-12-11  product1            700     530     170  4490.00    5593.50     1103.50            6.414286
2023-12-12  product1            700     595     105  4490.00    6250.00     1760.00            6.414286
2023-12-01  product2            250      80     170  1850.00    1200.00     -650.00            7.400000
2023-12-02  product2            250     140     110  1850.00    2112.00      262.00            7.400000
2023-12-04  product2            550     140     410  4010.00    2112.00    -1898.00            7.290909
2023-12-07  product2            550     175     375  4010.00    2619.50    -1390.50            7.290909
2023-12-10  product2            750     235     515  5610.00    3525.50    -2084.50            7.480000
2023-12-11  product2            750     330     420  5610.00    4884.00     -726.00            7.480000
2023-12-12  product2            750     370     380  5610.00    5480.00     -130.00            7.480000     */

- RickJames
- January 15, 2024 at 1:25 am
- 0 votes
0
For this kind of problem, I like to use two tables:
- History — a complete list of each addition or removal from the inventory.
- Current — the current quantity in inventory.
Then I would encapsulate the INSERT INTO History and UPDATE Current together in an app subroutine or an SQL "stored procedure". That would help assure me that I did not sneak in and modify one table without suitably updating the other table simultaneously. Also, by wrapping those two DML statements in BEGIN...COMMIT, the tables would be ACID.

Your task turns into a simple query into Current instead of a complicated, slow, search trough History.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Tracking inventory and purchase price of products over time

Answers