Forum Discussion
Excel - Matching outgoing to incoming inventory based a specific conditions
create temp table aa as
select *,sum(`Total pieces`+`Total out`) over (partition by `Supplier PO`) Balance from Sheet2;
select *, case when Balance=0 then "closed" when Balance=`Total pieces` then "Open" else "In Process" end Status from aa;
select * from Sheet2;
create temp table aa as
select rowid old_rowid,*,sum(`Total pieces`+`Total out`) over (partition by `Supplier PO`) Balance from Sheet2;
create temp table bb as
select *, case when Balance=0 then "closed" when Balance=`Total pieces` then "Open" else "In Process" end Status from aa;
create temp table cc as
select row_number() over () in_out,`Supplier PO` from bb group by `Supplier PO`;
select iif(`Total out`<0,'',in_out) `In`,iif(`Total out`<0,in_out,'') Out,* from cc join bb using(`Supplier P
O`);
Auto allocate First In First Out batch number:
1,in running balance
2,out running balance
3,match in and out balance
select * from Sheet2 limit 20;
create temp table aa as
select rowid old_rowid,*,row_number() over ( partition by SKU,PO) batch,sum(Pieces) over(order by rowid) in_balance from Sheet2 where Pieces>0;
create temp table bb as
select rowid old_rowid,*,sum(Total_out) over(order by rowid) balance from Sheet2 where Pieces=0;
select * from aa;
select * from bb;
create temp table cc as
select *,(select first_value(b.batch) over () from aa b where b.SKU=a.SKU and b.PO=a.PO and b.in_balance>=-a.balance) in_batch from bb a;
select * from cc;
select Sheet2.rowid,Sheet2.*,aa.batch,cc.in_batch from Sheet2 left join aa on Sheet2.rowid =aa.old_rowid left join cc on Sheet2.rowid=cc.old_rowid;