Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table that contains items with Start and End dates (Table 1 below) that I need to link to a table that contains a single date for each item. Basically if Table 1 has a StartDate of 10/1 and EndDate of 10/5, I need it to be associated with an item in Table 2 with all of the dates in between (10/1, 10/2, 10/3, 10/4, 10/5). I believe the solution is to generate rows for all of the "in between" dates, but I'm not sure how to do this. Any help you can provide would be much appreciated! Thank you -
Table 1: | |
StartDate | EndDate |
10/1/2017 | 10/5/2017 |
10/7/2017 | 10/10/2017 |
10/15/2017 | 10/20/2017 |
Table 2: |
Date |
10/1/2017 |
10/2/2017 |
10/3/2017 |
10/4/2017 |
10/5/2017 |
10/6/2017 |
10/7/2017 |
Etc… |
May be this?
LOAD Date(StartDate + IterNo() -1) as Date
While StartDate + IterNo() -1 <= EndDate;
LOAD * Inline [
StartDate, EndDate
10/1/2017, 10/5/2017
10/7/2017, 10/10/2017
10/15/2017, 10/20/2017
];
Maybe this can help you. Don't focus on the answer read all comments.
Hope it helps
Lluís
May be this?
LOAD Date(StartDate + IterNo() -1) as Date
While StartDate + IterNo() -1 <= EndDate;
LOAD * Inline [
StartDate, EndDate
10/1/2017, 10/5/2017
10/7/2017, 10/10/2017
10/15/2017, 10/20/2017
];
If You want from 10/1/2017 to 10/20/2017 then
SET DateFormat='MM/DD/YYYY';
LOAD Date(StartDate+IterNo()-1) as Date
While StartDate+IterNo()-1 <= EndDate;
LOAD Min(StartDate) as StartDate,Max(EndDate) as EndDate
Inline [
StartDate, EndDate
10/1/2017, 10/5/2017
10/7/2017, 10/10/2017
10/15/2017, 10/20/2017];
Thank you! This worked perfectly!