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!
|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|
|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|