Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: insert data based on where the transaction stop

    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:

    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,
            MAX(DATE_ENTRY_WEIGHING) AS DATE_ENTRY_VIRTUAL,
            MAX(DATE_ENTRY_DIPPING) AS DATE_ENTRY_KANBAN,
            MAX(DATE_ENTRY_WEIGHING) AS DATE_ENTRY_TOTAL,
            
            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..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •