I have the following table:
Schedules
+----+---------+------------+-------+--------------------------+
| id | user_id | project_id | hours | date_range |
+----+---------+------------+-------+--------------------------+
| 1 | 1 | 1 | 4 | [2019-05-01, 2019-05-08) |
+----+---------+------------+-------+--------------------------+
| 2 | 2 | 1 | 8 | [2019-04-20, 2019-05-20) |
+----+---------+------------+-------+--------------------------+
| 3 | 1 | 2 | 2 | [2019-05-05, 2019-05-12) |
+----+---------+------------+-------+--------------------------+
| ...etc | | | |
+--------------+------------+-------+--------------------------+
What I was hoping to accomplish with SQL was to get a summation of total hours per week given a specific date range input, for example date_start: 2019-05-05, date_end: 2019-05-11.
For the week of 2019-05-05 to 2019-05-11, any Schedule that has a date_range that has overlapping dates would get summated by project_id and by user_id.
Example: Row 1 from the above table has a date_range of [2019-05-01, 2019-05-08) so it would have 2019-05-05, 2019-05-06, 2019-05-07 (2019-05-08 omitted because exclusive )) dates that match the date_start/date_end criterion.
Since the hours are 4 for that row, the given total would be 4 * n days = 12 hours total
The date_start/date_end can be arbitrarily large, so say it was over a year range, every date_range would be added by week over the given range.
Potential expected output (open to suggestions):
| week_start | week_end | project_id | user_id | total_hours |
| 2019-05-05 | 2019-05-11 | 1 | 1 | 12 |
| 2019-05-05 | 2019-05-11 | 1 | 2 | 56 |
| etc ... | | | | |
I'm not quite sure how to structure a query to generate matching date_rage days and multiply them out per user.
Can someone point me in the right direction?
