# Counter like tag with cumulated value

**URL:** <https://forum.inductiveautomation.com/t/counter-like-tag-with-cumulated-value/65051>\
**Category:** Ignition\
**Created:** [October 11, 2022, 6:40am UTC](https://forum.inductiveautomation.com/t/counter-like-tag-with-cumulated-value/65051 "2022-10-11T06:40:55Z")\
**Posts on this page:** 1\
**Showing post:** 12

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 11, 2022, 12:43pm UTC](https://forum.inductiveautomation.com/t/counter-like-tag-with-cumulated-value/65051/12 "2022-10-11T12:43:17Z")

</div>

The reliable way to do this is to record state changes in an event table. Then use `LEAD` or `LAG` functions in the DB to get intervals per state, clipping to the start of shift. Sum those intervals while grouping by state. With an index on t\_stamp, your DB will calculate this blisteringly fast.

```auto
Select state, sum(event_duration) as duration
From (
  Select state, nextTS - clipped as event_duration
  From (
    Select state
      , case t_stamp < :shift_start_ts then :shift_start_ts else t_stamp end as clipped
      , coalesce(lead(t_stamp) over (Order by t_stamp), current_timestamp) as nextTS
    From state_events
    Where t_stamp >= (Select max(t_stamp) Where t_stamp < :shift_start_ts)
  ) intervals
) durations

```

Display the final duration from the row that matches the current state.

---

_[View the full topic](https://forum.inductiveautomation.com/t/counter-like-tag-with-cumulated-value/65051)._