Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Source data looks like this:
%Id | %Date | Start | Stop | Hrs |
7 | 2019-01-01 | 2019-01-01 08:12 | 2019-01-01 10:16 | 2,07 |
5 | 2019-01-01 | 2019-01-01 10:16 | 2019-01-02 18:52 | 32,60 |
3 | 2019-01-02 | 2019-01-02 18:52 | 2019-01-08 11:23 | 136,52 |
7 | 2019-01-08 | 2019-01-08 11:23 | 2019-03-25 08:43 | 1821,33 |
I’ve seen in other threads how to split date and time intervals respectively, but here we have date and time.
The result should look like this, i.e. round timestamps to the nearest hour and insert rows:
%Id | %Date | Start | Stop | Hrs |
7 | 2019-01-01 | 2019-01-01 08:00 | 2019-01-01 09:00 | 1 |
7 | 2019-01-01 | 2019-01-01 09:00 | 2019-01-01 10:00 | 1 |
5 | 2019-01-01 | 2019-01-01 10:00 | 2019-01-01 11:00 | 1 |
5 | 2019-01-01 | 2019-01-01 11:00 | 2019-01-01 12:00 | 1 |
5 | 2019-01-01 | 2019-01-01 12:00 | 2019-01-01 13:00 | 1 |
5 | 2019-01-01 | 2019-01-01 13:00 | 2019-01-01 14:00 | 1 |
5 | 2019-01-01 | 2019-01-01 14:00 | 2019-01-01 15:00 | 1 |
5 | 2019-01-01 | 2019-01-01 15:00 | 2019-01-01 16:00 | 1 |
5 | 2019-01-01 | 2019-01-01 16:00 | 2019-01-01 17:00 | 1 |
5 | 2019-01-01 | 2019-01-01 17:00 | 2019-01-01 18:00 | 1 |
5 | 2019-01-01 | 2019-01-01 18:00 | 2019-01-01 19:00 | 1 |
5 | 2019-01-01 | 2019-01-01 19:00 | 2019-01-01 20:00 | 1 |
5 | 2019-01-01 | 2019-01-01 20:00 | 2019-01-01 21:00 | 1 |
5 | 2019-01-01 | 2019-01-01 21:00 | 2019-01-01 22:00 | 1 |
5 | 2019-01-01 | 2019-01-01 22:00 | 2019-01-01 23:00 | 1 |
5 | 2019-01-01 | 2019-01-01 23:00 | 2019-01-02 00:00 | 1 |
5 | 2019-01-02 | 2019-01-02 00:00 | 2019-01-02 01:00 | 1 |
5 | 2019-01-02 | 2019-01-02 01:00 | 2019-01-02 02:00 | 1 |
5 | 2019-01-02 | 2019-01-02 02:00 | 2019-01-02 03:00 | 1 |
5 | 2019-01-02 | 2019-01-02 03:00 | 2019-01-02 04:00 | 1 |
5 | 2019-01-02 | 2019-01-02 04:00 | 2019-01-02 05:00 | 1 |
5 | 2019-01-02 | 2019-01-02 05:00 | 2019-01-02 06:00 | 1 |
5 | 2019-01-02 | 2019-01-02 06:00 | 2019-01-02 07:00 | 1 |
5 | 2019-01-02 | 2019-01-02 07:00 | 2019-01-02 08:00 | 1 |
5 | 2019-01-02 | 2019-01-02 08:00 | 2019-01-02 09:00 | 1 |
5 | 2019-01-02 | 2019-01-02 09:00 | 2019-01-02 10:00 | 1 |
5 | 2019-01-02 | 2019-01-02 10:00 | 2019-01-02 11:00 | 1 |
5 | 2019-01-02 | 2019-01-02 11:00 | 2019-01-02 12:00 | 1 |
5 | 2019-01-02 | 2019-01-02 12:00 | 2019-01-02 13:00 | 1 |
5 | 2019-01-02 | 2019-01-02 13:00 | 2019-01-02 14:00 | 1 |
5 | 2019-01-02 | 2019-01-02 14:00 | 2019-01-02 15:00 | 1 |
5 | 2019-01-02 | 2019-01-02 15:00 | 2019-01-02 16:00 | 1 |
5 | 2019-01-02 | 2019-01-02 16:00 | 2019-01-02 17:00 | 1 |
5 | 2019-01-02 | 2019-01-02 17:00 | 2019-01-02 18:00 | 1 |
5 | 2019-01-02 | 2019-01-02 18:00 | 2019-01-02 19:00 | 1 |
3 | 2019-01-02 | 2019-01-02 19:00 | 2019-01-02 20:00 | 1 |
3 | 2019-01-02 | 2019-01-02 20:00 | 2019-01-02 21:00 | 1 |
3 | 2019-01-02 | 2019-01-02 21:00 | 2019-01-02 22:00 | 1 |
3 | 2019-01-02 | 2019-01-02 22:00 | 2019-01-02 23:00 | 1 |
3 | 2019-01-02 | 2019-01-02 23:00 | 2019-01-03 00:00 | 1 |
3 | 2019-01-03 | 2019-01-03 00:00 | 2019-01-03 01:00 | 1 |
3 | 2019-01-03 | 2019-01-03 01:00 | 2019-01-03 02:00 | 1 |
... and so on.
What should the script expression look like when using ITERNO() to iterate the combined datetime stamp?
I didn't test this, Technically this seems works
LOAD %Id, %Date, Start, Stop, Hrs, MaxStop, MinStart + IterNo() -1 as MinStart While MinStart + IterNo() -1<= MaxStop;
Tab:
LOAD %Id,
Date(%Date, 'DD/MM/YYYY') as %Date,
Date(Replace(Start, Minute(Start), '00'),'DD/MM/YYYY hh:mm') as Start,
Time(Date(Replace(Start, Minute(Start), '00'),'DD/MM/YYYY hh:mm'),'hh') as MinStart,
Date(Replace(Stop, Minute(Stop), '00'),'DD/MM/YYYY hh:mm') as Stop,
Time(Date(Replace(Stop, Minute(Stop), '00'),'DD/MM/YYYY hh:mm'),'hh') as MaxStop,
Hrs Inline [
%Id, %Date, Start, Stop, Hrs
7, 2019-01-01, 2019-01-01 08:12, 2019-01-01 10:16,
5, 2019-01-01, 2019-01-01 10:16, 2019-01-02 18:52,
3, 2019-01-02, 2019-01-02 18:52, 2019-01-08 11:23,
7, 2019-01-08, 2019-01-08 11:23, 2019-03-25 08:43,
];