Sample Header Ad - 728x90

How to summate weekly totals from a postgres date range type per user

0 votes
1 answer
453 views
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?
Asked by tr3online (113 rep)
May 31, 2019, 06:15 AM
Last activity: Oct 8, 2024, 02:06 PM