Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
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;
OMG....