Trip duration from Log Dates

[johngouws]

Good Day. The below data shows an extract of a track log for trips. What I need to determine is the number of 'Trip Days'. For example, for trip aaaa all the dates of the 27th will be day 1, the 29th will be day 2, the 30th will be day 3 and so on. I need to end up with trip aaaa being 4 days and trip bbbb 3 days. The issue I am having is that I cannot get the counter variable to step correctly. The attached qvf has the script I am using. I appreciate the assistance. 

Trip, Time
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 19:59
aaaa, 2018-07-29 07:49
aaaa, 2018-07-29 07:49
aaaa, 2018-07-29 07:50
aaaa, 2018-07-29 07:50
aaaa, 2018-07-29 07:50
aaaa, 2018-07-30 15:03
aaaa, 2018-07-30 15:03
aaaa, 2018-07-30 15:03
aaaa, 2018-07-30 15:04
aaaa, 2018-07-31 08:12
aaaa, 2018-07-31 08:12
aaaa, 2018-07-31 08:12
aaaa, 2018-07-31 08:12
bbbb, 2018-07-27 06:08
bbbb, 2018-07-27 06:08
bbbb, 2018-07-27 06:08
bbbb, 2018-07-27 06:08
bbbb, 2018-07-27 06:08
bbbb, 2018-07-30 15:03
bbbb, 2018-07-30 15:04
bbbb, 2018-07-31 08:12
bbbb, 2018-07-31 08:12
bbbb, 2018-07-31 08:12
bbbb, 2018-07-31 08:12];

[jonathandienst]

Extract the Date from the date/time value and use the extended autonumber for this:

t1:
LOAD Trip,	
    Date(Floor(Date#(Time, 'yyyy-MM-dd hh:mm'))) as Date,
    Time(Frac(Date#(Time, 'yyyy-MM-dd hh:mm'))) as Time 
INLINE [ 
Trip,	Time
aaaa,	2018-07-27 06:08
aaaa,	2018-07-27 06:08
...<snip>...
bbbb,	2018-07-31 08:12];

t2:
LOAD 
    Trip,
    Date, 
    Time,
    AutoNumber(Date, Trip) as [Trip Day]
Resident t1 

Drop Table t  

[jonathandienst · Accepted solution]

[johngouws]

Thank you Jonty. Works perfectly. Who could have thought I could be so far off.