How do I calculate the sum of a field filtered by multiple windows
defined by another field?
I have table event with fields event_date, num_events, site_id. I can
easily use aggregate SQL to do SELECT SUM(num_events) GROUP BY
site_id.
But I also have another table site with fields site_id, target_date.
I'd like to do a JOIN, showing the SUM of num_events within 60 days of
the target_date, 90 days, 120 days, etc. I thought this could easily
be done using a WHERE clause in the aggregate SQL. However, this is
complicated by two challenges:
1. The target_date is not fixed, but varies for each site_id
2. I'd like multiple date ranges to be outputed in the same table; so
I can't do a simple WHERE to exclude records falling outside the range
from the event table
One workaround I've thought of is to simply make several queries, one
for each date range, using a different WHERE clause for each, and then
use a view to paste them together. Is there a simpler, better, or more
elegant way to achieve my goals?