# How to Calculate total Runtime and StopTime from Timestamp in MSSQL

**URL:** <https://forum.inductiveautomation.com/t/how-to-calculate-total-runtime-and-stoptime-from-timestamp-in-mssql/67873>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [December 21, 2022, 2:11pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-total-runtime-and-stoptime-from-timestamp-in-mssql/67873 "2022-12-21T14:11:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ganesh\_Shinde1](https://avatars.discourse-cdn.com/v4/letter/g/5f8ce5/32.png) [@Ganesh\_Shinde1](https://forum.inductiveautomation.com/u/Ganesh_Shinde1)\
**Post date:** [December 21, 2022, 2:11pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-total-runtime-and-stoptime-from-timestamp-in-mssql/67873/1 "2022-12-21T14:11:52Z")

</div>

Hi  
I Have a Status Tag of an Machine with values of Running, Idle, Downtime , these Tag's I am storing in MSSQL dB using Transaction group , Now I have to calculate the total time that the status is in running mode and total time it is in downtime mode I want output to be like (Running = 52 sec) (Downtime = 11 sec)  
Need Some Help On this .  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/2/929f89712e44b1e45fb14dbf3f13c147e32c7d19.png)

---

<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:** [December 21, 2022, 2:26pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-total-runtime-and-stoptime-from-timestamp-in-mssql/67873/2 "2022-12-21T14:26:25Z")

</div>

You use your DB's `lead()` or `lag()` functions with some subquery nesting. Examples lifted from a recent nearly identical question:

> [@Calculating Total Time in Seconds](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/4):
>
> I usually do all of this in the database with lead() and/or lag() window functions. Say you have multiple of these recording to a single table, using an id column to segregate them. You record every change, and due to tag restarts or script restarts you get some duplicate rows as you’ve shown. The query to pick out all of the completed cycles that overlap a time period would be something like this: Select id, t\_stamp, finish\_ts, finish\_ts - t\_stamp As duration From ( Select id, t\_st…

> [@SQL Query Group data in Table 2 by time events in Table 1](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/8):
>
> I presume you want to ignore repeat ones and zeros in the output column? If so, running periods from table1 would be something like this: Select t\_stamp as start\_ts, lead(t\_stamp, 1, current\_timestamp) Over (Order by t\_stamp) as end\_ts From ( Select t\_stamp, output From ( Select t\_stamp, output, lag(output, 1, 0) Over (Order By t\_stamp) as lag\_out From table1 ) innerq Where output != lag\_out ) changesq Where output = 1 Order By start\_ts And similarly, downtime pe…

And [more here](https://forum.inductiveautomation.com/search?q=%22lead%28%22).

---

<div class="post-metadata">

**Author:** ![Ganesh\_Shinde1](https://avatars.discourse-cdn.com/v4/letter/g/5f8ce5/32.png) [@Ganesh\_Shinde1](https://forum.inductiveautomation.com/u/Ganesh_Shinde1)\
**Post date:** [December 26, 2022, 7:04am UTC](https://forum.inductiveautomation.com/t/how-to-calculate-total-runtime-and-stoptime-from-timestamp-in-mssql/67873/3 "2022-12-26T07:04:15Z")

</div>

Finally succeeded

Thanks.