Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello to all,
from a table made like this :
ID | StartDate | EndDate | Ora_inizio | Ora_fine |
a | 02/03/2018 09:59:23 | 02/03/2018 10:04:53 | 09:59 | 10:04 |
b | 02/03/2018 09:02:23 | 02/03/2018 09:03:53 | 09:02 | 09:03 |
c | 02/03/2018 09:03:13 | 02/03/2018 09:05:43 | 09:03 | 09:05 |
I would like to have a table like that:
ID | StartDate | EndDate | Ora_inizio | Ora_fine |
a | 02/03/2018 09:59:23 | 02/03/2018 10:04:53 | 09:59 | 10:04 |
a | 02/03/2018 09:59:23 | 02/03/2018 10:04:53 | 10:00 | 10:04 |
a | 02/03/2018 09:59:23 | 02/03/2018 10:04:53 | 10:01 | 10:04 |
a | 02/03/2018 09:59:23 | 02/03/2018 10:04:53 | 10:02 | 10:04 |
a | 02/03/2018 09:59:23 | 02/03/2018 10:04:53 | 10:03 | 10:04 |
a | 02/03/2018 09:59:23 | 02/03/2018 10:04:53 | 10:04 | 10:04 |
b | 02/03/2018 09:02:23 | 02/03/2018 09:03:53 | 09:02 | 09:03 |
b | 02/03/2018 09:02:23 | 02/03/2018 09:03:53 | 09:03 | 09:03 |
c | 02/03/2018 09:03:13 | 02/03/2018 09:05:43 | 09:03 | 09:05 |
c | 02/03/2018 09:03:13 | 02/03/2018 09:05:43 | 09:04 | 09:05 |
c | 02/03/2018 09:03:13 | 02/03/2018 09:05:43 | 09:05 | 09:05 |
Someone could help me.
Thanks in advance
After you load you table, try this:
Concatenate
Load ID,
StartDate,
EndDate,
Time(Num(Time#(Ora_inizio,'HH:mm')) + (IterNo()/(24*60)),'HH:mm') as Ora_inizio,
Ora_fine
Resident YourTableName While IterNo() <= ((Num(Time#(Ora_fine,'HH:mm')) - Num(Time#(Ora_inizio,'HH:mm'))) * 24*60) + 1;
something like below..
Temp:
Load
ID,
TimeStamp#(StartDate,'DD/MM/YYYY hh:mm:ss') as StartDate,
TimeStamp#(EndDate,'DD/MM/YYYY hh:mm:ss') as EndDate,
Time#(Ora_inizio,'hh:mm') as Ora_inizio,
Time#(Ora_fine,'hh:mm') as Ora_fine
Inline
[
ID, StartDate, EndDate, Ora_inizio, Ora_fine
a, 02/03/2018 09:59:23, 02/03/2018 10:04:53, 09:59, 10:04
b, 02/03/2018 09:02:23, 02/03/2018 09:03:53, 09:02, 09:03
c, 02/03/2018 09:03:13, 02/03/2018 09:05:43, 09:03, 09:05
];
NoConcatenate
Final:
Load
ID, StartDate, EndDate,
Time(Ora_inizio + Time((IterNo()-1)/24/60),'hh:mm') as Ora_inizio,
Ora_fine
Resident Temp
While Time(Ora_inizio + Time((IterNo()-1)/24/60),'hh:mm') <= Ora_fine
;
Drop Table Temp;
Both solutions are fine
Thank you