Create retrospective summary report of record counts over time

Hello all,

I'm having some trouble conceptualizing the best way to achieve a report that would provide a retrospective look over time at the number of active assignments on any given date, to allow us to track changes in various related metrics over time.

Basically I have a table of technician assignments where each assignment has a start date and an end date, and the same for vehicle assignments (and there are probably other things we may want to apply this concept to in the future).

What I would like to end up with is a report that can query and display the number of active assignments as of, say, each Friday this year. To do this, I need some way to test whether each Friday date falls after the start date and before the end date of each assignment, then count the assignments where this is true.

I've tried coming at this via a summary report, considered a pipeline, and attempted a record query formula field, but these each seem to fit well for one piece of the equation then get stuck on another.

I know I could just set up a pipeline to record the counts on a daily basis, but it's not unusual that adjustments are made after the fact so I'd prefer something more dynamic. The idea I hit on was to create a "Daily Records" table with a record for each day of the year and a formula field to count the assignment records that encompass the date assigned to each record. 

The issue that I'm hitting on is that I can't find a way to do a summary field that summarizes records that fall between the values in two fields (and there may not even be an end date input on many records, just to add a little more complexity). I thought a record query formula field would do the trick, but I didn't have any luck with the size or count functions and I'm not sure how else I could go at it.

Any ideas are welcome! I'm sure I'm not the first to want to do this, but I haven't found the magic search terms if the solution is already out there.