Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to generate some information between two dates, Please refer the below example help me to achieve the same
example
Tool Id | Assign Date | Running Date |
1234 | 5-Mar-20 | 30-Dec-20 |
5678 | 10-Dec-20 | 12-Feb-21 |
I want result like this
Tool ID | Running Month |
1234 | Mar-20 |
1234 | Apr-20 |
1234 | May-20 |
1234 | Jun-20 |
1234 | Jul-20 |
1234 | Aug-20 |
1234 | Sep-20 |
1234 | Oct-20 |
1234 | Nov-20 |
1234 | Dec-20 |
5678 | Dec-20 |
5678 | Jan-21 |
5678 | Feb-21 |
Thanks in advance
Siraj
Try this:
TEST:
LOAD * INLINE [
RunningDateolID, AssignDate, RunningDate
1234, 05/03/2020, 30/12/2020
5678, 10/12/2020, 12/02/2021
];
NoConcatenate
tmp:
LOAD
RunningDateolID,
Date(AddMonths(AssignDate,IterNo()-1)) as AssignDate,
Date(AddMonths(AssignDate,IterNo()-1)) as RunningDate
Resident TEST
While AddMonths(AssignDate,IterNo()-1) <= RunningDate;
Final:
Load *, Date(RunningDate,'MM/YYYY') as DateNew Resident tmp; DROP Tables TEST,tmp
Thanks Frank,
This logic works perfectly
Thanks
Siraj