SELECT COUNT(*) FROM (SELECT i.item_code, i.item_category_name, i.description,
ifnull(sum(CASE WHEN p.warehouse_id = 2 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 2 THEN p.out END),0) AS 001_WHOUSE,
ifnull(sum(CASE WHEN p.warehouse_id = 5 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 5 THEN p.out END),0) AS MPH_WHOUSE,
ifnull(sum(CASE WHEN p.warehouse_id = 6 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 6 THEN p.out END),0) AS 003_RJT_WHOUSE,
ifnull(sum(CASE WHEN p.warehouse_id = 7 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 7 THEN p.out END),0) AS GR8_WHOUSE,
ifnull(sum(CASE WHEN p.warehouse_id = 10 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 10 THEN p.out END),0) AS 002_TMP_WHOUSE,
ifnull(sum(CASE WHEN p.warehouse_id = 12 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 12 THEN p.out END),0) AS POP_WHOUSE,
ifnull(sum(CASE WHEN p.warehouse_id = 15 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 15 THEN p.out END),0) AS YOU_LIN_WHOUSE,
ifnull(sum(CASE WHEN p.warehouse_id = 18 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 18 THEN p.out END),0) AS STICKERRIFIC_WHOUSE,
ifnull(sum(CASE WHEN p.warehouse_id = 21 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 21 THEN p.out END),0) AS CZIPLEE_WHOUSE
FROM 0p4_item_transaction p
JOIN 0p4_item i ON p.item_id = i.item_id
JOIN 0p4_warehouse w ON p.warehouse_id = w.warehouse_id where p.warehouse_id IN (2,5,6,7,10,12,15,18,21)and p.tran_date <= '2022-10-14' and i.item_code >= 'PLU-TG 728R-LS 6M' and i.type_id = 1 and i.item_code <= 'PLU-TG 728R-LS 6M' GROUP BY i.item_id) `sub`







SELECT i.item_code, i.item_category_name, i.description,
ifnull(sum(CASE WHEN p.warehouse_id = 2 THEN p.in END),0) - ifnull(sum(CASE WHEN p.warehouse_id = 2 THEN p.out END),0) AS 001_WHOUSE,
FROM 0p4_item_transaction p
JOIN 0p4_item i ON p.item_id = i.item_id
JOIN 0p4_warehouse w ON p.warehouse_id = w.warehouse_id where p.warehouse_id IN (2,5,6,7,10,12,15,18,21)and p.tran_date <= '2022-10-14' and i.item_code >= 'PLU-TG 728R-LS 6M' and i.type_id = 1 and i.item_code <= 'PLU-TG 728R-LS 6M' GROUP BY i.item_id




/** Select for chekcking */
select y.item_id,x.item_code,y.warehouse_id,x.total_bal as stockbal, 
sum(ifnull(y.quantity,0)) as phyqty 
from 0p4_item_physical_quantity y 
left join (SELECT *, sum( (ifnull(`in`,0))- (ifnull(`out`,0))) as 'total_bal' 
FROM `0p4_item_transaction` `tran` where tran_date <= '2022-10-14' GROUP BY `item_id`,`warehouse_id`) x 
on x.item_id=y.item_id 
and x.warehouse_id=y.warehouse_id GROUP by y.item_id,y.warehouse_id 
HAVING phyqty!=total_bal



/** Update */
update 0p4_item_physical_quantity p
left join ( select y.item_id,x.item_code,y.warehouse_id,x.total_bal as stockbal, 
sum(ifnull(y.quantity,0)) as phyqty 
from 0p4_item_physical_quantity y 
left join (SELECT *, sum( (ifnull(`in`,0))- (ifnull(`out`,0))) as 'total_bal' 
FROM `0p4_item_transaction` `tran` GROUP BY `item_id`,`warehouse_id`) x 
on x.item_id=y.item_id 
and x.warehouse_id=y.warehouse_id GROUP by y.item_id,y.warehouse_id 
HAVING phyqty!=total_bal) t on t.item_id=p.item_id and p.warehouse_id=t.warehouse_id
set p.quantity=t.stockbal
where t.item_id is not null