I have my select code:
SELECT m.`genmat_id`,m.`mat_type`,m.`mat_name`, m.`category`, m.`size`, m.`req_qty`, m.`stock_received`, m.`stock_released`, m.`release_approval`,
(select sum(stock_received) - sum(stock_released)
from genmaterial m2
where m2.mat_name = m.mat_name and
m2.genmat_id <= m.genmat_id
) as Stock_balance,
(case when (select sum(stock_received)
from genmaterial m2
where m2.mat_name = m.mat_name and
m2.genmat_id <= m.genmat_id
) = req_qty
then 'Complete'
when req_qty = 0 then ''
else 'Incomplete'
end) as status,
m.`date`,
s.`sup_name`
FROM `genmaterial` m LEFT JOIN
`supplier` s
on s.sup_id = m.sup_id
ORDER BY m.`genmat_id` ASC
OUTPUT(disregard the Full Texts column):
Full Texts genmat_id mat_type mat_name category size req_qty stock_received stock_released release_approval Stock_balance status date sup_name
Edit Delete 69 pur aluminum flat bar metal 100 m 130 0 0 0 Incomplete 2014-02-14 E-store
Edit Delete 70 Raw aluminum flat bar metal 100 m 130 50 0 50 Incomplete 2014-02-15 E-store
Edit Delete 71 Raw aluminum flat bar metal 100 m 130 80 0 130 Complete 2014-02-20 E-store
Edit Delete 72 pur mild steel metal 100 m 50 0 0 0 Incomplete 2014-02-13 Handy Shop
Edit Delete 73 Raw mild steel metal 100 m 50 50 0 50 Complete 2014-02-15 Handy Shop
Edit Delete 74 pur mild steel metal 100 m 40 0 0 50 Incomplete 2014-02-14 E-store
Edit Delete 77 Raw mild steel metal 100 m 40 40 0 90 Incomplete 2014-02-17 E-store
The mat_id 74 is a new request identified by mat_type = pur and the mat_id 77 is the receiving of the request with 40 on stock_received and mat_type = Raw. Now, the stock_balance is just so right because it Sums up the previous total of the material + the stock_received.
My question: How can I make the status complete with this condition?
it will sum up the stock_received when mat_type=raw inside a counter and if the counter=req_qty will have the status=complete My existing code generates a status when Stock_balance = req_qty. How can I do it counter = req_qty and the stock_balance is from the sum of counter every mat_name also the stock_balance - stock_released(stock_out)
like(see counter column): The counter will only count the stock_received per new mat_type=pur
Full Texts genmat_id mat_type mat_name category size req_qty stock_received counter stock_released release_approval Stock_balance status date sup_name
Edit Delete 69 pur aluminum flat bar metal 100 m 130 0 0 0 0 Incomplete 2014-02-14 E-store
Edit Delete 70 Raw aluminum flat bar metal 100 m 130 50 50 0 50 Incomplete 2014-02-15 E-store
Edit Delete 71 Raw aluminum flat bar metal 100 m 130 80 130 0 130 Complete 2014-02-20 E-store
Edit Delete 72 pur mild steel metal 100 m 50 0 0 0 0 Incomplete 2014-02-13 Handy Shop
Edit Delete 73 Raw mild steel metal 100 m 50 50 50 0 50 Complete 2014-02-15 Handy Shop
Edit Delete 74 pur mild steel metal 100 m 40 0 0 0 50 Incomplete 2014-02-14 E-store
Edit Delete 77 Raw mild steel metal 100 m 40 40 40 0 90 Incomplete 2014-02-17 E-store