Need Help in getting only data which can suffix the total Qty

[Illusion]

What do you I did not alter the data?What table or should i alter?

And also what table or data should I update?

 

I thought dipping table get updated with new quantities ... to zero if completely utilized or to a new number if it partially used.

 

 

If you are not updating the table.. it is very tricky to correlate with table where you inserting the data and display the data for next JO.

 

try this..  I don't have access to mysql server now so I didn't test the code .

 

SELECT SUBSTR(LOT_CODE, 9,4) as pcode, LOT_CODE as code, op_qty,  DATE_ENTRY,   CASE WHEN @t+op_qty > @rqty THEN @rqty  - @t  ELSE op_qty END as qty,@t := @t + d.op_qty as cumulative  from 

((select LOT_CODE,output_qty -qty as op_qty,date_entry from jo_dump where output_qty > qty and SUBSTR(LOT_CODE, 9,4) = '$Comp' order by date_entry desc limit 1) UNION

(select LOT_CODE,output_qty as op_qty ,date_entry from dipping  where date_entry >(select ifnull(date_entry, '0000-00-00') from jo_dump where output_qty > qty and SUBSTR(LOT_CODE, 9,4) = '$Comp' order by date_entry desc limit 1))) d  WHERE (@t < @rqty);

 

Looking at my query I can tell you that...tables design is wrong.

[Barand]

@newphpcoder,

 

See my php code for this mysql query in your other thread on this subject in another forum

 

http://www.phpfreaks.com/forums/index.php?topic=359370.msg1699211#msg1699211.

 

This is the last help from me - having two threads just wastes people's time.

[newphpcoder]

What do you I did not alter the data?What table or should i alter?

And also what table or data should I update?

 

I thought dipping table get updated with new quantities ... to zero if completely utilized or to a new number if it partially used.

 

 

If you are not updating the table.. it is very tricky to correlate with table where you inserting the data and display the data for next JO.

 

try this..  I don't have access to mysql server now so I didn't test the code .

 

SELECT SUBSTR(LOT_CODE, 9,4) as pcode, LOT_CODE as code, op_qty,  DATE_ENTRY,   CASE WHEN @t+op_qty > @rqty THEN @rqty  - @t  ELSE op_qty END as qty,@t := @t + d.op_qty as cumulative  from 

((select LOT_CODE,output_qty -qty as op_qty,date_entry from jo_dump where output_qty > qty and SUBSTR(LOT_CODE, 9,4) = '$Comp' order by date_entry desc limit 1) UNION

(select LOT_CODE,output_qty as op_qty ,date_entry from dipping  where date_entry >(select ifnull(date_entry, '0000-00-00') from jo_dump where output_qty > qty and SUBSTR(LOT_CODE, 9,4) = '$Comp' order by date_entry desc limit 1))) d  WHERE (@t < @rqty);

 

Looking at my query I can tell you that...tables design is wrong.

 

 

I tried this no error but no output display.

 

 

Thank you

[newphpcoder]

I tried to think on how can i resolved it, how can I code it to resolve this problem and yet I can't figured out or think what code should resolve my problem.

 

 

Thank you for your effort to help me..Sorry im not good in logic and coding

[fenway]

0 := 0?

 

I don't think so.