Reset a cumulative sum when Timestamp field LIKE

JDriscoll — Wed, 28 Sep 2022 14:30:46 GMT

Hi,

I have a table with 5 columns:

One with a timestamp incrementing in 15 minutes per row (time_15m)
One counting entries (in_count)
One counting exits (out_count)
One summing both ins and outs (count_sum)
One with a cumulative sum of count_sum (cumulative)

time_15m	in_count	out_count	count_sum	cumulative
2022-01-01 00:00:00+00	0	0	0	0
2022-01-01 00:15:00+00	2	0	2	2
2022-01-01 00:30:00+00	0	1	-1	1
2022-01-01 00:45:00+00	3	1	2	3
2022-01-01 01:00:00+00	1	1	0	3
2022-01-01 01:15:00+00	0	2	-2	1

The query for cumulative is as follows:

rangesum( above( sum(count_sum),0,rowno(TOTAL)))

I need cumulative to reset to 0 every time a new day begins, like so:

time_15m	in_count	out_count	count_sum	cumulative
2022-01-01 23:30:00+00	0	0	0	3
2022-01-01 23:45:00+00	0	1	-1	2
2022-01-02 00:00:00+00	0	0	0	0
2022-01-02 00:15:00+00	1	0	1	1
2022-01-02 00:30:00+00	2	1	1	2

The query currently has the cumulative column constantly accumulating regardless of the time_15m value. Either this query can reset the cumulative column to 0 every midnight of every day, or to add a value to either in_count or out_count to make cumulative sum to 0 depending on the values of the other fields in the table.

How can this query be modified to achieve this?

Thank you.

topic Reset a cumulative sum when Timestamp field LIKE in App Development

Reset a cumulative sum when Timestamp field LIKE