I have the following data. I am trying to number the amount of trips per each day in the time that they happened from earliest to latest.
This data is loaded as PefromanceData
VehicleID |
DatafromDateTime |
Data To Date Time |
Trip Count |
Date |
14 |
2022-02-13 : |
2022/02/13 |
35 |
2022-02-13 |
14 |
2022-02-13 : |
2022/02/13 |
34 |
2022-02-13 |
14 |
2022-02-13 : |
2022/02/13 |
33 |
2022-02-13 |
14 |
2022-02-13 : |
2022/02/13 |
32 |
2022-02-13 |
18 |
2022-02-13 : |
2022/02/13 |
61 |
2022-02-13 |
18 |
2022-02-13 : |
2022/02/13 |
84 |
2022-02-13 |
19 |
2022-02-13 : |
2022/02/13 |
98 |
2022-02-13 |
19 |
2022-02-13 : |
2022/02/13 |
82 |
2022-02-13 |
20 |
2022-02-13 : |
2022/02/13 |
66 |
2022-02-13 |
20 |
2022-02-13 : |
2022/02/13 |
50 |
2022-02-13 |
20 |
2022-02-13 : |
2022/02/13 |
40 |
2022-02-13 |
20 |
2022-02-13 : |
2022/02/13 |
15 |
2022-02-13 |
14 |
2022-02-12 : |
2022/02/12 |
105 |
2022-02-12 |
14 |
2022-02-12 : |
2022/02/12 |
96 |
2022-02-12 |
20 |
2022-02-12 : |
2022/02/12 |
87 |
2022-02-12 |
20 |
2022-02-12 : |
2022/02/12 |
125 |
2022-02-12 |
PerformanceData:
Load
*
,
if (Previous(VehicleID) = VehicleID and Previous(floor(Day)) = floor(Day), peek('DayTripNo') + 1, 1) as DayTripNo
Resident PerformanceData1
order by VehicleID,Day,[DatafromDateTime];
Drop table PerformanceData1;