Hi..
I have problem in my select query statement with case statement to compute the qty and I tried evetything to resolved my problem.
here is my query:
first I tried the requirement qty is less than in balance qty.
@rqty = requirement qty
@q= balalce qty
set @t = 0;
set @rqty = 900;
set @r = 563;
set @q = 966;
SELECT SUBSTR(d.LOT_CODE, 9,4) as pcode, d.LOT_CODE as code, d.OUTPUT_QTY, d.DATE_ENTRY,
CASE
WHEN d.LOT_CODE = j.lotcode AND @rqty > j.bal_qty
THEN j.bal_qty
WHEN d.LOT_CODE = j.lotcode AND @rqty < j.bal_qty
THEN @rqty
WHEN @rqty < @t + d.OUTPUT_QTY
THEN (@rqty - @q)
WHEN @t+d.OUTPUT_QTY > @rqty
THEN (@rqty - @q)
ELSE d.OUTPUT_QTY END as qty,
@t := @t + d.OUTPUT_QTY as cumulative
FROM dipping_dump d, dependency c, jo_last j
WHERE NOT EXISTS (SELECT j.lotcode, j.pcode FROM jo_dump j WHERE j.lotcode = d.LOT_CODE AND j.pcode = SUBSTR(d.LOT_CODE, 9,4) AND j.qty = d.OUTPUT_QTY) AND c.LOT_CODE = d.LOT_CODE AND c.DATE_ENTRY = d.DATE_ENTRY AND c.TERMINAL_ID = '5' AND c.ACTIVE = '1' AND SUBSTR(d.LOT_CODE, 9,4) = 'P35' AND (@t < @rqty) AND j.STATUS = '1' ORDER BY d.DATE_ENTRY ASC;
I attached the sample image output.
I got a problem when the requirement qty is less than output qty and greater balance qty.
I hope this time someone can understand and help me.
Thank you so much..
