JOIN in temporary table

[Anonymous]

Hello,

i have a problem with my script.

I create the table "LAST_TIME" that contains 3 keys (IDDEVICE, IDPARK and IDFIELD) and extract the max DATETIME from the table GENERAL_INPUTS_DATA

LAST_TIME:

LOAD

IDDEVICE,   IDPARK,   IDFIELD,

  MAX([DATETIME]) AS CURR_TIME

RESIDENT GENERAL_INPUTS_DATA

GROUP BY IDDEVICE, IDPARK, IDFIELD;

Example of result

IDPARK	IDDEVICE	IDFIELD	CUR_TIME
1	a	1	15/04/2016 12:00
1	b	1	15/04/2016 11:00
2	c	1	13/04/2016 06:00
2	d	1	15/04/2016 12:00

The table GENERAL_INPUT_DATA contains also data about ENERGY YELDED  and i would obtain the ENERGY producted today.

LOAD   Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY

RESIDENT       GENERAL_INPUTS_DATA

WHERE Year(DATETIME) = Year(Today(1))

AND Month(DATETIME) = Month(Today(1))

AND Day(DATETIME) = Day(Today(1))

GROUP BY IDDEVICE, IDPARK, IDFIELD;

Example of result

IDPARK	IDDEVICE	IDFIELD	ENERGY YELDED
1	a	1	50,00
1	b	1	20,00
2	c	1	0,00
2	d	1	13,00

How can i join the 2 table to obtain the next result ?

IDPARK	IDDEVICE	IDFIELD	CUR_TIME	ENERGY YELDED
1	a	1	15/04/2016 12:00	50,00
1	b	1	15/04/2016 11:00	20,00
2	c	1	13/04/2016 06:00	0,00
2	d	1	15/04/2016 12:00	13,00

In MySql i would use a query like this but with "RESIDENT" i can't use ALIAS (in this example, calling GENERAL_INPUTS_DATA   G1  or G2  )

select g1.IDDEVICE,   g1.IDPARK,

  MAX(g1.`DATETIME`) AS CURR_TIME,

(

select Max(g2.`ENERGY YELDED`) - Min(g2.`ENERGY YELDED`) as CURR_ENERGY_TODAY

from `GENERAL INPUTS DATA` g2

WHERE

Year(g2.DATETIME) = Year(now(1)) AND Month(g2.DATETIME) = Month(now(1)) AND Day(g2.DATETIME) = Day(now(1))

and g2.IDPARK = g1.IDPARK  and g1.IDDEVICE = g2.IDDEIVCE

GROUP BY g2.IDDEVICE, g2.IDPARK

)

from `GENERAL INPUTS DATA` g1

GROUP BY g1.IDDEVICE, g1.IDPARK;

[swuehl · Accepted solution]

Have you tried just joining your tables?

LAST_TIME:

LOAD

IDDEVICE,   IDPARK,   IDFIELD,

  MAX([DATETIME]) AS CURR_TIME

RESIDENT GENERAL_INPUTS_DATA

GROUP BY IDDEVICE, IDPARK, IDFIELD;

JOIN (LAST_TIME)

LOAD   Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY

,IDDEVICE, IDPARK, IDFIELD RESIDENT       GENERAL_INPUTS_DATA

WHERE Year(DATETIME) = Year(Today(1))

AND Month(DATETIME) = Month(Today(1))

AND Day(DATETIME) = Day(Today(1))

GROUP BY IDDEVICE, IDPARK, IDFIELD;

[Kushal_Chawda]

try this

LAST_TIME:

LOAD

IDDEVICE,  IDPARK,  IDFIELD,

  MAX([DATETIME]) AS CURR_TIME

RESIDENT GENERAL_INPUTS_DATA

GROUP BY IDDEVICE, IDPARK, IDFIELD;

join(LAST_TIME)

LOAD  IDDEVICE, IDPARK, IDFIELD,

            Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY

RESIDENT  GENERAL_INPUTS_DATA

WHERE floor(DATETIME) = floor(today())

GROUP BY IDDEVICE, IDPARK, IDFIELD;

[Anonymous]

OMG....