How can distribute one table data to another table-CodePudding

I have two tables Orderapproval and stock:

Table 1: Orderapproval

itemcode	approvalqty
1	25
2	10

Table 2: stock

itemcode	stockqty	batch	date
1	5	aa	2021-02-01
1	10	bb	2021-02-10
1	15	cc	2021-02-02
2	5	dd	2021-02-01
2	20	aa	2021-02-05

we have stock batch-wise. when we get an order request for an item we are trying to pick up items from the old batch first & so on How can I get a result set like this:

itemcode	qty	batch	date
1	5	aa	2021-02-01
1	15	cc	2021-02-02
1	5	bb	2021-02-10
2	5	dd	2021-02-01

CodePudding user response：

Try this:

SELECT s.itemcode
     , s.stockqty AS qty
     , s.batch
     , s.date
  FROM stock AS s
 INNER JOIN Orderapproval AS oa
    ON s.stockqty <= oa.approvalqty

If required, insert resultset into new table

CodePudding user response：

SQL CODE---(Tested)

Select stock.itemcode,stock.stockqty AS qty,stock.batch,stock.date from stock join orderapproval on stock.stockqty <= orderapproval.approvalgty group by stock.batch;

CodePudding user response：

I try it on SQLite 3.30

Schema

CREATE TABLE stock (
  id INT,
  itemcode INT,
  stockqty INT,
  batch CHAR,
  date DATE
);
INSERT INTO stock (id, itemcode, stockqty, batch, date) VALUES (1, 1, 5, 'aa', '2021-02-01');
INSERT INTO stock (id, itemcode, stockqty, batch, date) VALUES (2, 1, 10, 'bb', '2021-02-10');
INSERT INTO stock (id, itemcode, stockqty, batch, date) VALUES (3, 1, 15, 'cc', '2021-02-02');
INSERT INTO stock (id, itemcode, stockqty, batch, date) VALUES (4, 2, 5, 'dd', '2021-02-01');
INSERT INTO stock (id, itemcode, stockqty, batch, date) VALUES (5, 2, 20, 'aa', '2021-02-05');

CREATE TABLE orderapproval (
  id INT,
  itemcode INT,
  approvalqty INT
);
INSERT INTO orderapproval (id, itemcode, approvalqty) VALUES (1, 1, 25);
INSERT INTO orderapproval (id, itemcode, approvalqty) VALUES (2, 2, 10);

Query

with a as ( SELECT *, sum(stockqty) over (partition by itemcode order by date ) as acc FROM stock)
select * from a 
inner join orderapproval o
on a.itemcode = o.itemcode
where acc <= approvalqty

Result

id	itemcode	stockqty	batch	date	acc	approvalqty
1	1	5	aa	2021-02-01	5	25
1	1	15	cc	2021-02-02	20	25
2	2	5	dd	2021-02-01	5	10

db-fiddle