I wanna create a new table from the data of two tables

I have a A table to store the product name, the quantity and input day of goods in stock. In adverse conditions, invoices for the above products will be sent later. I will put the product name and invoice quantity in B table. The problem here is that I want to check the quantity of goods with invoice and without invoice. Updating goods with invoices will follow FIFO.

Example:

Table A

id	good_id	num	created_at
1	1	10	2021-09-24
2	1	5	2021-09-25

Table B

id	good_id	num_invoice
1	1	12

I solved it by creating a new C table with the same data as the A table

Table C

id	good_id	current_number	created_at	invoice_number
1	1	10	2021-09-24	null
2	1	5	2021-09-25	null

Then I get the data in table B group by good_id and store it in $data. Using php to foreach $data and check condition:

I updated C table ORDER BY created_at DESC limit 1 as follows:

• if (tableC.current_num – $data['num'] < 0) then update current_number = 0, invoice_number = $data['num'] – tableC.current_num. Update value $data['num'] = $data['num'] – tableC.current_num

• if (tableC.current_num – $data['num'] > 0) or (tableC.current_num – $data[‘num’] = 0) then update current_number = tableC.current_num – $data['num'], invoice_number = $data['num'].

table C after update

id	good_id	current_number	created_at	invoice_number
1	1	0	2021-09-24	10
2	1	3	2021-09-25	2

I solved the problem with php like that. However, with a dataset of about 100,000 rows, I think the backend processing will take a long time. Can someone give me a smarter way to handle this?

Answer

Updated solution for MySQL 5.7:

Test case to support MySQL 5.7+, PG, MariaDB prior to 10.2.2, etc:

Test case for MySQL 5.7+, etc

For MySQL 5.7: This replaces the window function (for running SUM) and uses derived tables instead of WITH clause.

SELECT id, good_id
     , num - GREATEST(num - GREATEST(balance, 0), 0) AS num
     , created_at
     , GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
  FROM (
         SELECT MIN(t2.id) AS id, MIN(t2.num) AS num
              , t2.good_id, t2.created_at
              , MIN(o.num_invoice) AS num_invoice
              , SUM(t1.num) - MIN(o.num_invoice) AS balance
           FROM tableA AS t1
           JOIN tableA AS t2
             ON t1.good_id = t2.good_id
            AND t1.created_at <= t2.created_at
           JOIN (
                   SELECT good_id, SUM(num_invoice) AS num_invoice
                     FROM tableB
                    GROUP BY good_id
                ) AS o
             ON o.good_id = t1.good_id
          GROUP BY t2.good_id, t2.created_at
       ) AS cte2
 ORDER BY created_at
;

For databases that handle functional dependence in GROUP BY properly, we could just GROUP BY t2.id (the primary key of tableA) and remove the MIN(t2.id) and the MIN(t2.num).

Like this:

Test case

-- For MySQL 5.7
SELECT id, good_id
     , num - GREATEST(num - GREATEST(balance, 0), 0) AS num
     , created_at
     , GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
  FROM (
         SELECT t2.id, t2.num
              , t2.good_id, t2.created_at
              , MIN(o.num_invoice) AS num_invoice
              , SUM(t1.num) - MIN(o.num_invoice) AS balance
           FROM tableA AS t1
           JOIN tableA AS t2
             ON t1.good_id = t2.good_id
            AND t1.created_at <= t2.created_at
           JOIN (
                   SELECT good_id, SUM(num_invoice) AS num_invoice
                     FROM tableB
                    GROUP BY good_id
                ) AS o
             ON o.good_id = t1.good_id
          GROUP BY t2.id
       ) AS cte2
 ORDER BY created_at
;

Original Answer using window functions and WITH clause:

Here’s a test case with PG 13, but works fine with MySQL 8 or MariaDB 10.2.2+.

Note: I left this as just a query that generates the requested detail. It’s not clear the 3rd table is necessary. This can be used to update (or create) that table, if needed.

Test case:

Working test case

CTE terms:

1. cte1 – calculate the total requested goods
2. cte2 – Calculate the running balance based on running inventory by date

Finally, we use cte2 to determine the goods allocated and remaining, requested by the question.

WITH cte1 (good_id, num_invoice) AS (
        SELECT good_id, SUM(num_invoice) AS num_invoice
          FROM tableB
         GROUP BY good_id
     )
   , cte2 AS (
       SELECT a.*, o.num_invoice
            , SUM(num) OVER (PARTITION BY a.good_id ORDER BY created_at) - o.num_invoice AS balance
         FROM tableA AS a
         JOIN cte1   AS o
           ON o.good_id = a.good_id
     )
SELECT id, good_id
     , num - GREATEST(num - GREATEST(balance, 0), 0) AS num
     , created_at
     , GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
  FROM cte2
 ORDER BY created_at
;

The result:

+----+---------+------+------------+-------------+
| id | good_id | num  | created_at | invoice_num |
+----+---------+------+------------+-------------+
|  1 |       1 |    0 | 2021-09-24 |          10 |
|  2 |       1 |    3 | 2021-09-25 |           2 |
|  3 |       1 |    7 | 2021-09-26 |           0 |
+----+---------+------+------------+-------------+

Note: I added one additional onhand entry for 7 goods (id = 3) to test an edge case.

Setup of the test case:

CREATE TABLE tableA (
   id           int primary key
 , good_id      int
 , num          int
 , created_at   date
);

CREATE TABLE tableB (
   id           int primary key
 , good_id      int
 , num_invoice  int
);

INSERT INTO tableA VALUES
  (1,1,10,'2021-09-24')
, (2,1, 5,'2021-09-25')
, (3,1, 7,'2021-09-26')
;

INSERT INTO tableB VALUES
  (1,1,12)
;