newphpcoder Posted July 16, 2012 Share Posted July 16, 2012 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.. Quote Link to comment Share on other sites More sharing options...
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.