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.
