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.
