insert data based on where the transaction stop

[newphpcoder]

Hi...

 

I have 6 transactions.

 

every transaction has date entry. Now I need to get the last dateentry from

DATE_ENTRY_WEIGHING

DATE_ENTRY_COMPOUNDING

DATE_ENTRY_EXTRUSION

DATE_ENTRY_FORMING then insert to DATE_ENTRY_VIRTUAL

 

and

 

DATE_ENTRY_WEIGHING

DATE_ENTRY_COMPOUNDING

DATE_ENTRY_EXTRUSION

DATE_ENTRY_FORMING

DATE_ENTRY_DIPPING

then insert to DATE_ENTRY_TOTAL.

 

here is my code:

 

$sql = "INSERT INTO kanban_open";
$sql =  $sql . " " . "SELECT REFNUM, LOT_CODE, 
        ROUND(SUM(WEIGHING_OUTPUT),2) AS WEIGHING, 
        ROUND(SUM(COMPOUNDING_OUTPUT),2) AS COMPOUNDING, 
        ROUND(SUM(EXTRUSION_OUTPUT),2) AS EXTRUSION, 
        ROUND(SUM(FORMING_OUTPUT),2) AS FORMING,
        ROUND(SUM(DIPPING_OUTPUT),2) AS DIPPING, 
        ROUND(SUM(MOULDING_OUTPUT),2) AS MOULDING, 

        MAX(DATE_ENTRY_WEIGHING) AS DATE_ENTRY_WEIGHING, 
        MAX(DATE_ENTRY_COMPOUNDING) AS DATE_ENTRY_COMPOUNDING, 
        MAX(DATE_ENTRY_EXTRUSION) AS DATE_ENTRY_EXTRUSION, 
        MAX(DATE_ENTRY_FORMING) AS DATE_ENTRY_FORMING, 
        MAX(DATE_ENTRY_DIPPING) AS DATE_ENTRY_DIPPING, 
        MAX(DATE_ENTRY_MOULDING) AS DATE_ENTRY_MOULDING,
   [b]     MAX(DATE_ENTRY_WEIGHING) AS DATE_ENTRY_VIRTUAL,[/b]
        MAX(DATE_ENTRY_DIPPING) AS DATE_ENTRY_KANBAN,
[b]        MAX(DATE_ENTRY_WEIGHING) AS DATE_ENTRY_TOTAL,[/b]
        
        ROUND(IF (NOT ISNULL(SUM(COMPOUNDING_OUTPUT)), 0, SUM(WEIGHING_OUTPUT)),2) AS WIP_1,
        ROUND(IF (NOT ISNULL(SUM(EXTRUSION_OUTPUT)), 0, SUM(COMPOUNDING_OUTPUT)),2) AS WIP_2,
        ROUND(IF (NOT ISNULL(SUM(FORMING_OUTPUT)), 0, SUM(EXTRUSION_OUTPUT)),2) AS WIP_3,
        ROUND(IF (NOT ISNULL(SUM(DIPPING_OUTPUT)), 0, SUM(FORMING_OUTPUT)),2) AS WIP_4,
        ROUND(IF (NOT ISNULL(SUM(MOULDING_OUTPUT)), 0, SUM(DIPPING_OUTPUT)),2) AS WIP_5,
        ROUND(SUM(MOULDING_OUTPUT),2) AS WIP_6,
        
        IF (NOT ISNULL(MAX(DATE_ENTRY_COMPOUNDING)), NULL, MAX(DATE_ENTRY_WEIGHING)) AS DATE_WIP_WEIGHING,
        IF (NOT ISNULL(MAX(DATE_ENTRY_EXTRUSION)), NULL, MAX(DATE_ENTRY_COMPOUNDING)) AS DATE_WIP_COMPOUNDING,
        IF (NOT ISNULL(MAX(DATE_ENTRY_FORMING)), NULL, MAX(DATE_ENTRY_EXTRUSION)) AS DATE_WIP_EXTRUSION,
        IF (NOT ISNULL(MAX(DATE_ENTRY_DIPPING)), NULL, MAX(DATE_ENTRY_FORMING)) AS DATE_WIP_FORMING,
        IF (NOT ISNULL(MAX(DATE_ENTRY_MOULDING)), NULL, MAX(DATE_ENTRY_DIPPING)) AS DATE_WIP_DIPPING,     
        MAX(DATE_ENTRY_MOULDING) AS DATE_WIP_MOULDING,
        IF (NOT ISNULL(MAX(DATE_ENTRY_COMPOUNDING)), NULL, MAX(DATE_ENTRY_EXTRUSION)) AS DATE_WIP_VIRTUAL,
        MAX(DATE_ENTRY_DIPPING) AS DATE_ENTRY_KANBAN,   
        IF (NOT ISNULL(MAX(DATE_ENTRY_COMPOUNDING)), NULL, MAX(DATE_ENTRY_EXTRUSION)) AS DATE_WIP_TOTAL,
        
        TIMEDIFF(MAX(DATE_ENTRY_COMPOUNDING), MAX(DATE_ENTRY_WEIGHING)) AS ELAPSED_1,
        TIMEDIFF(MAX(DATE_ENTRY_EXTRUSION), MAX(DATE_ENTRY_COMPOUNDING)) AS ELAPSED_2,
        TIMEDIFF(MAX(DATE_ENTRY_FORMING), MAX(DATE_ENTRY_EXTRUSION)) AS ELAPSED_3,
        TIMEDIFF(MAX(DATE_ENTRY_DIPPING), MAX(DATE_ENTRY_FORMING)) AS ELAPSED_4,
        TIMEDIFF(MAX(DATE_ENTRY_MOULDING), MAX(DATE_ENTRY_DIPPING)) AS ELAPSED_5

        FROM MES_REPORT_MAIN GROUP BY REFNUM HAVING ISNULL(SUM(MOULDING_OUTPUT))";

mysql_query($sql,$con);

 

I hope somebody can help me on this.

 

Thank you so much..

[fenway]

How many threads can you open on what appears to be the exact same query?