Need mysql query FIFO

Question

please help me how to create mysql query for reporting in fifo method,

table persediaan_source

id	id_barang	jumlah	harga	tanggal	id_jenis_transaksi
89	26	12	1050000	2022-07-15 05:55:23	1
90	26	8	0	2022-07-15 05:55:52	2
91	26	16	1100000	2022-07-15 05:56:22	1
95	26	10	0	2022-07-15 05:59:09	2

id_jenis_transaksi = 1 is Buy
id_jenis_transaksi = 2 is Use

i need report like this below

id	date	remarks	buy_qty	buy_price	buy_total	use_qty	use_qty_detail	use_price	use_total	bal_qty	bal_qty_detail	bal_price	bal_total
1	2022-07-15 05:55:23	Buy	12	1050000	12600000	0	0	0	0	12	12	1050000	12600000
2	2022-07-15 05:55:52	Use	0	0	0	8	8	1050000	8400000	4	4	1050000	4200000
3	2022-07-15 05:56:22	Buy	16	1100000	17600000	0	0	0	0	20	4	1050000	4200000
4	2022-07-15 05:56:22	Buy	0	0	0	0	0	0	0	0	16	1100000	17600000
5	2022-07-15 05:59:09	Use	0	0	0	10	4	1050000	4200000	10	0	1050000	0
6	2022-07-15 05:59:09	Use	0	0	0	0	6	1100000	6600000	0	10	1100000	11000000

in row #3 must be breakdown in bal_qty_detail because there is a different price and have a remaining qty from a previous price, also in row #5 must be breakdown in use_qty_detail

CREATE TABLE `persediaan_source` (
  `id` int(11) NOT NULL,
  `id_barang` int(11) NOT NULL,
  `jumlah` double NOT NULL,
  `harga` double NOT NULL,
  `tanggal` datetime NOT NULL,
  `id_jenis_transaksi` tinyint(4) NOT NULL COMMENT 'id = 1 -> buy, id = 2 -> use'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `persediaan_source` (`id`, `id_barang`, `jumlah`, `harga`, `tanggal`, `id_jenis_transaksi`) VALUES
(89, 26, 12, 1050000, '2022-07-15 05:55:23', 1),
(90, 26, 8, 0, '2022-07-15 05:55:52', 2),
(91, 26, 16, 1100000, '2022-07-15 05:56:22', 1),
(95, 26, 10, 0, '2022-07-15 05:59:09', 2);

Maybe [this](https://dba.stackexchange.com/questions/154592/calculate-quantity-based-on-first-in-first-out-fifo) could help — Asgar, Jul 19 '22 at 15:32
I've tried that link before I asked for this help, the link doesn't match what I need, on the link it doesn't show the price — Dodo Ananto, Jul 20 '22 at 23:48
What is the logic for row 4 and 6? Why are columns 0 in row-4 and 6. Use related column=0 in row for buy is understandable, but in this case almost all columns are 0! — Pankaj, Jul 21 '22 at 02:05
I think this report has a problem in terms of logic. Rule to generate report is not clear. — Liki Crus, Jul 25 '22 at 09:46
It can be achieved by using variables, Kindly refer the given [link](https://stackoverflow.com/questions/51391820/how-to-increment-a-variable-value-in-select-clause-in-sql-server). — Nandan Rana, Jul 26 '22 at 15:34
@NandanRana, how do you create extra rows than available rows/ — Asgar, Jul 26 '22 at 15:35
I suggest you do it in application code. SQL is quite messy when iterating through rows; it would prefer to work on all the rows at once. — Rick James, Jul 26 '22 at 16:04

Nandan Rana · Answer 1 · 2022-07-27T10:12:55.937

Hi using the below process you can achieve the mention reporting format. But to handle the granularity of fifo you should aviod creating these logic in mysql.

SELECT @bal_qty:=0,@old_qty:=0,@old_qty_price:=0,@new_qty:=0,@new_qty_price:=0;
CREATE TEMPORARY TABLE test1
SELECT id, tanggal as date, 
@remarks:=if(id_jenis_transaksi=1,'Buy','Use') as remarks, 
@buy_qty:=if(id_jenis_transaksi=1,jumlah,0) as buy_qty, 
@buy_price:=if(id_jenis_transaksi=1,harga,0) as buy_price,  
@buy_total:=(@buy_qty * @buy_price) as buy_total, 
@use_qty:=if(id_jenis_transaksi=2,jumlah,0) as use_qty, 
@use_qty_detail:=if(@remarks='Use',if(@old_qty>0,@old_qty, @use_qty),0) as use_qty_detail, 
@use_price:=@old_qty_price as use_price, 
@use_total:=(@use_qty * @use_price) as use_total, 
@bal_qty:=if(@remarks='Buy',@bal_qty + @buy_qty, if(@bal_qty>@use_qty,@bal_qty - @use_qty, @bal_qty)) as bal_qty, 
@old_qty:=if(@old_qty > 0, @old_qty, if(@remarks='Use',@bal_qty, @old_qty)) as old_qty,
@old_qty_price:=if(@old_qty_price > 0, @old_qty_price, @buy_price) as old_qty_price,  
@new_qty:=if(@old_qty>0,@buy_qty, if(@new_qty>0, @new_qty,0)) as new_qty,
@new_qty_price:=if(@old_qty_price > 0 and @buy_price > 0, @buy_price, if(@new_qty_price>0,@new_qty_price,0)) as new_qty_price,
@bal_qty_detail:=if(@old_qty > 0, @old_qty, @buy_qty) as bal_qty_detail, 
@bal_price:=@old_qty_price as bal_price,
@bal_total:=(@bal_qty_detail* @old_qty_price) as bal_total,
@split_flag:=if(@bal_qty != @buy_qty and @bal_qty != @old_qty,1,0) as split_flag 
FROM persediaan_source;

CREATE TEMPORARY TABLE test2 select * from test1 where split_flag=1

SELECT @cnt:=0;

SELECT (@cnt:=@cnt + 1) as id, date, remarks, buy_qty, buy_price, buy_total, use_qty, use_qty_detail, use_price, use_total, bal_qty, bal_qty_detail, bal_price, bal_total 
FROM (
     (SELECT * FROM test1)
UNION ALL
(SELECT id, date, remarks, 0 as buy_qty, 0 as buy_price, 0 as buy_total, 0 as use_qty, 
@used_qty:=(use_qty - use_qty_detail) as use_qty_detail, 
if(remarks='Use',@new_qty_price,0) as use_price, 
(@used_qty * new_qty_price)  as use_total, 0 as bal_qty, 0 as old_qty, 
0 as old_qty_price, 0 as new_qty,0 as new_qty_price, 
@bal_qty_detail:=if(remarks='Buy', buy_qty,  bal_qty) as bal_qty_detail, 
new_qty_price as bal_price, 
(@bal_qty_detail * new_qty_price) as bal_total, split_flag 
 FROM test2)
) as t order by 2

The OP has mentioned the table, data and required output. I highly suggest you to give the solution rather than some suggestions — Asgar, Jul 26 '22 at 15:43
@Asgar for base table i.e (persediaan_source) i have used the same create table and insert query mentioned in problem statement. In my solution i have created temporary table and instead of insert I have used select to store the output of query for futher use. I had to create test2 table, As test1 was temporary table and you can't reuse same temporary table in union query. — Nandan Rana, Jul 28 '22 at 10:06

Need mysql query FIFO

1 Answers1