we have a data that, vehicle can move to different locations and some times it might back to same location after x no of days.
Here i need to track the vehicle by finding out days the vehicle stayed in the same location.
Approach1: calculate min & max dates aggregated with Vehicle (ID) & Location - here got wrong result since vehcile can come to previous locations after some time.
Approach2: using interrecord function (Peek) able to find start date, but failed to derive End date for the same location
here we need to calculate no of days in the same sequence as date started.
please find the sample data in the excel with input data & expected output.
Thanks in advance.