using date generator over multiple years

[chrismtb]

Hi, I have some code (below) which is causing me a headache.

I have a Gantt Chart that I am pulling together. Each of my projects has a start date (GanttStart) and an end date (GanttEnd). I have written a code loop to add records in the gaps between the start and end date. These are in monthly increments with each project starting on the 1st of the month.

e.g. a project starts on 01/01/21 and ends 01/12/21 therefore the code will add a data point for 01/02/21, 01/03/21 etc.

This works great - as long as the start and end date are both this year. My issue is that i have some projects starting in January 2021 and ending in November 2022.

Can someone suggest how i can approach the problem?

an extract of my current code:

GANTT_CELLS:
Load
GanttId,
MonthCounter,
GanttStart,
GanttEnd,
Status,
;
Load
Distinct GanttId,
MakeDate(2021,((Month(GanttStart)-1)+IterNo()),01) as MonthCounter,
GanttStart,
GanttEnd,
1 as Status
Resident GANTT_Data
While
IterNo() <= Month(GanttEnd) - Month(GanttStart) + 1;

[marcus_sommer · Accepted solution]

You need to consider the years, too. Maybe with something like this:

...
Monthstart(GanttStart, IterNo()-1) as MonthCounter,
...
While
IterNo() <= (year(GanttEnd) * 12 + Month(GanttEnd)) - (year(GanttStart) * 12 + Month(GanttStart) + 1);

- Marcus

[chrismtb]

Thanks Marcus - works like a charm!