Script with max, min and total values

[josemaria_cabre]

Hi,

Let's imagine we load this data in our script:

LOAD * INLINE [
id, date, hour
a1, 01/01/2024, 8:00
a1, 01/01/2024, 9:00
a1, 01/01/2024, 10:24
a1, 01/01/2024, 11:56
a1, 02/01/2024, 7:34
a1, 02/01/2024, 9:00
a1, 02/01/2024, 10:24
a1, 02/01/2024, 14:55
b1, 02/01/2024, 1:02
b1, 02/01/2024, 3:33
b1, 02/01/2024, 4:44
b1, 02/01/2024, 6:25
];

And we'd like to get a summary resident table with 3 calculated fields on it, for each id and date: min date_hour, max_date_hour and total_time:

id	date	min_date_hour	max_date_hour	total_time
a1	01/01/2024	8:00	11:56	3:56
a1	02/01/2024	7:34	14:55	7:21
b1	02/01/2024	1:02	6:25	5:23

Is it possible to calculate these fields directly in the script? How would I acchieve that?

Thanks in advance for your help
Jose

[VBD · Accepted solution]

Hello,

You can try this,

TMP_TABLE:

LOAD * INLINE [

id, date, hour

a1, 01/01/2024, 8:00

a1, 01/01/2024, 9:00

a1, 01/01/2024, 10:24

a1, 01/01/2024, 11:56

a1, 02/01/2024, 7:34

a1, 02/01/2024, 9:00

a1, 02/01/2024, 10:24

a1, 02/01/2024, 14:55

b1, 02/01/2024, 1:02

b1, 02/01/2024, 3:33

b1, 02/01/2024, 4:44

b1, 02/01/2024, 6:25

];

TABLE:

Load

id,

    date,

    time(Max(hour)) as Max_Hour,

    time(Min(hour)) as Min_Hour

Resident TMP_TABLE

group by id,date;

Drop table TMP_TABLE ;

exit script;

Regards,

[vuan · Accepted solution]

Hi,

you can try this

LOAD *,
interval(max- min, 'hh:mm') as diff;
LOAD
id
, date,
interval(min(hour), 'hh:mm') as min,
interval(max(hour), 'hh:mm') as max
Group by id, date;
LOAD * INLINE [
id, date, hour
a1, 01/01/2024, 8:00
a1, 01/01/2024, 9:00
a1, 01/01/2024, 10:24
a1, 01/01/2024, 11:56
a1, 02/01/2024, 7:34
a1, 02/01/2024, 9:00
a1, 02/01/2024, 10:24
a1, 02/01/2024, 14:55
b1, 02/01/2024, 1:02
b1, 02/01/2024, 3:33
b1, 02/01/2024, 4:44
b1, 02/01/2024, 6:25
];

Kind regards

[josemaria_cabre]

Both solutions worked great, 

thank you @vuan @VBD !!