How to propogate Month Year between Start and End Date

[Anonymous]

Hi Experts,

@sunny_talwar@Gysbert_Wassenaar@swuehl@hic@jagan

I am facing an issue while propagating Month-Year between Start and End Date.

Below is the sample data:

StartDate	EndDate	ID
10/1/2017	11/5/2017	1
10/15/2017	12/20/2017	2

Output Required:

StartDate	EndDate	ID	Month Year
10/1/2017	11/5/2017	1	Oct-17
10/1/2017	11/5/2017	1	Nov-17
10/15/2017	1/20/2018	2	Oct-17
10/15/2017	1/20/2018	2	Nov-17
10/15/2017	1/20/2018	2	Dec-17
10/15/2017	1/20/2018	2	Jan-18

Below is the query I am using to propagate Dates. But I want to propagate the same logic on Month-Year level because I can't propagate on Date level due to big data size.

LOAD ID,
     Date(StartDate+IterNo()-1) as Date,
     StartDate,
     EndDate
While StartDate+IterNo()-1 <= EndDate;
LOAD ID,Min(StartDate) as StartDate,Max(EndDate) as EndDate
Inline [
ID,StartDate, EndDate
1,10/1/2017, 11/5/2017
2,10/15/2017, 12/20/2017]
Group By ID;

Thanks,

Ahmed

[tresesco]

Try like:

LOAD ID,
     MonthName(StartDate+IterNo()-1) as MonthNames,

...

[Anonymous]

@tresescoThanks for your prompt reply. I have tried it but it didn't work. Can you check on your side and share me your script. Thanks

[prat1507]

Solution by @tresesco should work.

Maybe this:

LOAD *, Date(StartDate,'MMM-YY') as [Month Year]

;

LOAD ID,
     Date(StartDate+IterNo()-1) as Date,
     StartDate,
     EndDate
While StartDate+IterNo()-1 <= EndDate;
LOAD ID,Min(StartDate) as StartDate,Max(EndDate) as EndDate
Inline [
ID,StartDate, EndDate
1,10/1/2017, 11/5/2017
2,10/15/2017, 12/20/2017]
Group By ID;

[Anonymous]

Thanks guys. I have tried the below script and it's giving me the result that I wanted.

SET DateFormat='MM/DD/YYYY';

LOAD ID,MonthName(StartDate,IterNo()-1) as MonthNames,StartDate,EndDate
While MonthName(StartDate,IterNo()-1) <= MonthName(EndDate);
LOAD ID,Min(StartDate) as StartDate,Max(EndDate) as EndDate
Inline [
ID,StartDate, EndDate
1,10/1/2017, 11/5/2017
2,10/15/2017, 12/20/2017]
Group By ID;

[tresesco]

PFA