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:


$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…

What is the problem with that query?

My problem in my query is in this part:


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.

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


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
    ,  ...