I have a table that has date ranges start_date and end_date i.e.
Start_Date End_Date
1/1/2009 5/5/2009
8/5/1986 6/7/1998
6/8/1995 10/10/2009
and so on.
What I'd like to do is take some day in the past, such as 1/1/1990 and then make a graph (line/bar) that sums the number of records in this table where thier date range is part of my criteria.
So in this case with a starting date of 1/1/1990 and ending date of current datetime
each of these records would contribute a daily count in some way or another...in some places thier date ranges overlap, thus producing counts of 2 or 3.
EDIT/ADDITION: I am using a master calendar table to store all possible dates between my start and end date. My problem is trying to figure out how to flag my data (the date ranges) by day...some sort of set of days that this interval defines.
Assign an ID to each range if you don't already have one, then count distinct Range IDs where the calendar Date is within the range.
count(distinct if(Date>=Start_Date and Date<=End_Date,Range_ID))
Another solution is probably closer to what you were thinking - use intervalmatch to explicitly build all of the days that each range defines. But that's a lot of data, and what if these were start and end timestamps instead of merely days? Then that approach would simply be unworkable. So I generally prefer handling this kind of case with an expression. Be warned that it is comparing every date to every range, so if you have a million dates and a million ranges, it will never finish, and you'll need some other solution.
