Select sum of one column based on next TRUE value of another column

[stefanasks]

This is a data design question. I need to know if it is possible to create a Select that summarize the values of one column based on one TRUE to the next TRUE value value of another column as shown in the image below in the computed value [sum_duration_interval]. 

My first Idea on this question was to first create a computed value in the same table of the sum since the last true value of [is_interval]. But that not only will ignore the first action, but also creates a huge performance problem.

My next Idea was to select the count of true [is_interval] values till the current date in a computed column and use it to group, and then summarize the [duration] in another computed column. However, even this would include the first section, there is still a huge select over the whole table on each row.

The reason what makes this a problem is, that the entry's can change, so that a I can not just calculate on insert a new row and that's it.

So, is there a simple way to compute the [sum_duration_interval] value dynamically or should I rethink the design?

[Suvrutt_Gurjar]

---

I think you are correct that it could be a bit lengthy implementation, given the requirement that 

---

@stefanasks wrote:

The reason what makes this a problem is, that the entry's can change, so that a I can not just calculate on insert a new row and that's it.

---

I think I could achieve it with a VC ( note the VC will be a multirow function VC that is used for indexing but could be sync time expensive) and a bot based action of type "set values of some columns in this row." The results are are follows.

The logic is based on the assumption that the dates are serial ( at least in increasing order) as shown in your shared screenshot. The logic is based on dates based indexing.

I believe it works even if an intermittent record's [is_interval] status is changed from True to false or vice versa. I will be pleased to share the test app with you if you wish to take a look or I can describe the solution here.

Please let me know.

[Suvrutt_Gurjar · Accepted solution]

I could achieve the functionality just with reference actions using bots. So no VC is necessary in computation. Please let me know and I can share the test app.

A combination of adds:

Another combination of adds:

[stefanasks]

Sorry for the delay, I was just to busy and tired to do anything.

So yes, if you still have this app I really would like to have a shared version of this solution. Please let me know how you want to accomplish the sharing process. 

[Suvrutt_Gurjar]

Sure. Please DM me the email ID to share the test app and I will share it.

[Xav]

Hello

I am also interested in this specific problem solution.

Is it possible to get the associated code/method ?

Thank you