PDA

View Full Version : insert data based on where the transaction stop



newphpbees
11 Apr 2012, 01:14 AM
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,
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..