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
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
  • I don't have time to type up an answer at this moment, can you look at this and confirm the result? dbfiddle.uk/…
    – bbaird
    Jul 27 '21 at 20:14
  • @bbaird thank you for taking the time to answer my question but I'm afraid it's still missing the same rows (2021-07-27 C2 and 2021-07-26 C2 and C2 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!
    – David Kerr
    Jul 27 '21 at 22:06
  • I see the issue now, we don't have a list of dates that would apply to the top level of the hierarchy. Let me chew on it a little more and get back to you.
    – bbaird
    Jul 28 '21 at 1:24
  • Ok, I think I have something that produces what you're looking for but I'm not 100% if that's the best way. Take a look here to confirm the output is at least correct: dbfiddle.uk/…
    – bbaird
    Jul 28 '21 at 2:23
  • Thanks for that updated query; it does indeed work correctly. However the SQL is beyond me and I don't understand it and on this project I must understand every line of code (and write equivalent not just "read only"). I'm going to return the simple day durations and do the aggregation in Kotlin code, which will be a very few lines of code. The result set won't be any larger this way. I shall add your fiddle as an answer in case any one else asks something similar. Thanks again, it's very impressive what SQL can do!
    – David Kerr
    Jul 28 '21 at 11:13

@bbaird gave a working solution in this DbFiddle.

As per my comment to my question I have decided to return the day durations and do the nested summation in code as it will be a very few simple lines.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.