Count of Items in Progress Using Dates

[#1]

Hello, I made this example data set to show the issue I am working through:

New Table("Project Status",
	Add Rows(5),
	New Column("Project", Character, "Nominal", Formula("Project " || Char(Row()))),
	New Column("Start Date", Numeric, "Ordinal", Format("m/d/y", 12), Formula(Date Increment( Today() - In Days( Row() * 10 ), "Day" ))),
	New Column("End Date", Numeric, "Ordinal", Format("m/d/y", 12), Formula(Date Increment( Today() + In Days( Row() * 10 ), "Day" )))
);

The following code will create a 5x3 Data Table with beginning and end dates for 5 different projects. What I would like to do is get a count of "projects in progress" for all month/year combos in the data set. By that I mean any project that's beginning date should be counted for that month until the end date has passed. Here is an image example of what I am hoping to produce:

Is there a way to get these counts into their own data table using JSL?

Please let me know if I can provide any more info on what I am trying to accomplish.

Thanks

[#2 · Accepted solution]

I would maybe write this as SQL query, but it can be also done in JSL. Below is one possible option (there are many simpler and more complicate ones)

Names Default To Here(1);

dt = New Table("Project Status",
	Add Rows(5),
	New Column("Project", Character, "Nominal", Formula("Project " || Char(Row()))),
	New Column("Start Date", Numeric, "Ordinal", Format("m/d/y", 12), Formula(Date Increment(Today() - In Days(Row() * 10), "Day"))),
	New Column("End Date", Numeric, "Ordinal", Format("m/d/y", 12), Formula(Date Increment(Today() + In Days(Row() * 10), "Day")))
);
dt << Run Formulas;
:Start Date << Delete Formula;
:End Date << Delete Formula;

// Add monthyear column based on Start Date
new_col1 = dt << New Column("MonthYear1",
	Numeric,
	"Ordinal",
	Format("m/y", 12),
	Formula(Date DMY(1, Month(:Start Date), Year(:Start Date)))
);

new_col2 = dt << New Column("MonthYear2",
	Numeric,
	"Ordinal",
	Format("m/y", 12),
	Formula(Date DMY(1, Month(:End Date), Year(:Start Date)))
);

// calculation should be based on min date and max date found from dataset in my opinion
// not based on the found values, but this is easier so I'll use this
date_months = Associative Array(new_col1);
date_months << Insert(Associative Array(new_col2));
dt << Delete Columns({"MonthYear1", "MonthYear2"});

aa_results = Associative Array();

For Each({month_start}, date_months << get keys,
	// last day of month
	max_day = In Days(Days In Month(Year(month_start), Month(month_start))) + month_start - Time Of Day(month_start);
	// some logic to get projects
	r = dt << Get Rows Where((:Start Date <= month_start & :End Date >= month_start) | (month_start <= :Start Date < max_day));
	aa_results[month_start] = N Items(r);
);

dt_results = New Table("Results",
	New Column("MY", Numeric, Continuous, Values(aa_results << get keys)),
	New Column("ProjInProg", Numeric, Continuous, Values(aa_results << get values)),
);