SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What is the problem with that query?

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    What is the problem with that query?
    My problem in my query is in this part:
    PHP Code:
    MAX(DATE_ENTRY_WEIGHING) AS DATE_ENTRY_VIRTUAL,     
    MAX(DATE_ENTRY_WEIGHING) AS DATE_ENTRY_TOTAL 
    it is wrong output.

    I need output for DATE_ENTRY_VIRTUAL is last date entry from
    DATE_ENTRY_WEIGHING
    DATE_ENTRY_COMPOUNDING
    DATE_ENTRY_EXTRUSION
    DATE_ENTRY_FORMING

    I need to know where the refnum was stop.

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry..

    I really don't know how can I insert DATE_ENTRY_VIRTUAL and DATE_ENTRY_TOTAL based where is the last DATE_ENTRY.

    Thank you for your help and understanding

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    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.
    Code:
    SELECT GREATEST(
             MAX(date_entry_weighing)
             MAX(date_entry_compounding)
             MAX(date_entry_extrusion)
             MAX(date_entry_forming)
                   ) AS date_entry_virtual
        ,  GREATEST(
             MAX(date_entry_weighing)
             MAX(date_entry_compounding)
             MAX(date_entry_extrusion)
             MAX(date_entry_forming)
             MAX(date_entry_dipping)
                   ) AS date_entry_total
        ,  ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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