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;
