I have a schema with activity(activity_id, parent_activity_id, name) and period(activity_id, duration, etc).
An activity can have ZERO to many associated periods. Further, activities have a reference to a parent references, making it hierarchical.
I am trying to write a query that will sum the durations per-activity and also "rollup" the periods from children, to the parent activity, and the grand-parent activity, recursively, so a parent's duration will include its own durations + the durations of all children (recursive). Initially the sum needs to be by day only, but I shall be adding by-week and by-month later.
The dbFiddle is a NEARLY working sample: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c0e92e9ffe4454c2c69a8bdccc6abe75
The problem is that the value for row 7 (C2) needs to include the duration for it's children (C2 P1 and C2 P1 T1). An entry for C2 is not present because there are ZERO periods against the activity.
How can I update the query or views such that activities with ZERO periods still include the sum of its child activities?
Thanks, this SQL is killing my poor imperative brain!
| row_number | activity_id | name | day_duration | date |
|---|---|---|---|---|
| 1 | 1 | C1 | 66 | 2021-07-26 00:00:00.000000 |
| 2 | 1 | C1 | 45 | 2021-07-27 00:00:00.000000 |
| 3 | 2 | C1 P1 | 34 | 2021-07-27 00:00:00.000000 |
| 4 | 2 | C1 P1 | 35 | 2021-07-26 00:00:00.000000 |
| 5 | 3 | C1 P1 T1 | 18 | 2021-07-26 00:00:00.000000 |
| 6 | 3 | C1 P1 T1 | 27 | 2021-07-27 00:00:00.000000 |
| 7 | 4 | C2 | NULL | NULL |
| 8 | 5 | C2 P1 | 50 | 2021-07-27 00:00:00.000000 |
| 9 | 6 | C2 P1 T1 | 39 | 2021-07-26 00:00:00.000000 |
| 10 | 6 | C2 P1 T1 | 39 | 2021-07-27 00:00:00.000000 |
C2and 2021-07-26C2andC2 P1). I should also have said that there can be an arbitrary number of activity levels, from 1 to n. I'll update the question tomorrow when I'm back at the computer. Thanks again!