Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody, I need your help!
I have a table with so many records like this:
ID , Start_Date, End_Date
Key1234, 01/03/2021, 04/03/2021
Key5678, 28/02/2021, 02/03/2021
And I would like to obtain:
Key1234, 01/03/2021
Key1234, 02/03/2021
Key1234, 03/03/2021
Key1234, 04/03/2021
Key5678, 28/02/2021
Key5678, 01/03/2021
Key5678, 02/03/2021
I mean, I would like to multiply/duplicate this line by the number of days between the interval of dates and for this example, obtain 4 records.
I tried to create a calendar with al the dates from 2019 to 2022 and use a left join and intervalmatch function to associate the two dates on the interval but there is too many records and the data load editor need too much time to load the data...
Can someone help me please ? Thanks in advance
Try this,
SET DateFormat='DD/MM/YYYY';
tab1:
LOAD *, Date(Start_Date+IterNo()-1) As Date
While Start_Date+IterNo()-1<=End_Date;
LOAD * INLINE [
ID, Start_Date, End_Date
Key1234, 01/03/2021, 04/03/2021
Key5678, 28/02/2021, 02/03/2021
];
Try this,
SET DateFormat='DD/MM/YYYY';
tab1:
LOAD *, Date(Start_Date+IterNo()-1) As Date
While Start_Date+IterNo()-1<=End_Date;
LOAD * INLINE [
ID, Start_Date, End_Date
Key1234, 01/03/2021, 04/03/2021
Key5678, 28/02/2021, 02/03/2021
];
It's working! Thank you!