Calculation time spent between multiple timestamps

[BusinessCo]

Hi,

I have data with multiple timestamp pro day. What i need is to calculate the time between first en last stamp pro day. In example below the tme between 07:04:09 and 14:31: 49 is 07:27:40.

Can somebody help me? Thanx!

License Plate	Time	Number of emptyings
59-BDS-6	07:04:09	1
59-BDS-6	07:51:15	1
59-BDS-6	08:06:26	1
59-BDS-6	08:41:22	1
59-BDS-6	09:56:20	1
59-BDS-6	09:59:08	1
59-BDS-6	10:59:29	1
59-BDS-6	10:59:50	1
59-BDS-6	11:24:30	1
59-BDS-6	11:25:40	1
59-BDS-6	13:56:40	1
59-BDS-6	13:59:48	1
59-BDS-6	14:23:36	1
59-BDS-6	14:31:49	1

The result must be:

License Plate	Total time spent	Number of emptyings
59-BDS-6	07:27:40	14

[tresesco · Accepted solution]

Time(max(Time#(Time, 'hh:mm:ss'))-Min(Time#(Time, 'hh:mm:ss')), 'hh:mm:ss')

[Taoufiq_Zarra]

One solution :

Data:

LOAD * INLINE [
    License Plate, Time, Number of emptyings
    59-BDS-6, 07:04:09, 1
    59-BDS-6, 07:51:15, 1
    59-BDS-6, 08:06:26, 1
    59-BDS-6, 08:41:22, 1
    59-BDS-6, 09:56:20, 1
    59-BDS-6, 09:59:08, 1
    59-BDS-6, 10:59:29, 1
    59-BDS-6, 10:59:50, 1
    59-BDS-6, 11:24:30, 1
    59-BDS-6, 11:25:40, 1
    59-BDS-6, 13:56:40, 1
    59-BDS-6, 13:59:48, 1
    59-BDS-6, 14:23:36, 1
    59-BDS-6, 14:31:49, 1
];

output:

load [License Plate],sum([Number of emptyings]) as [Number of emptyings], Interval ( (time(max(Time)))  -(time(min(Time))) , 'hh:mm:ss' ) as [Total time spent] resident Data group by [License Plate]; 

drop table Data;

output :

[NitinK7 · Accepted solution]

Hi,

try like below

dimension- License Plate

Expression_1(Total time spent)-  Time(FirstSortedValue(Time,-Time)-FirstSortedValue(Time,Time),'hh:mm:ss').

Expression_2(Number of emptyings)- count([Number of emptyings]).

See below screenshot

Thanks

Nitin.

[BusinessCo]

Thank you very much!