# Historical machine status on pie chart

**URL:** <https://forum.inductiveautomation.com/t/historical-machine-status-on-pie-chart/17597>\
**Category:** Ignition\
**Created:** [April 4, 2018, 7:03am UTC](https://forum.inductiveautomation.com/t/historical-machine-status-on-pie-chart/17597 "2018-04-04T07:03:33Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![dgoetschalckx](https://avatars.discourse-cdn.com/v4/letter/d/67e7ee/32.png) [@dgoetschalckx](https://forum.inductiveautomation.com/u/dgoetschalckx)\
**Post date:** [April 4, 2018, 7:03am UTC](https://forum.inductiveautomation.com/t/historical-machine-status-on-pie-chart/17597/1 "2018-04-04T07:03:33Z")

</div>

I need to make a pie chart with the historical status of the machine.  
the different status are:  
0: Stop  
1: Run  
2: Slow Run

It is important that I can specify the time between which the status should be displayed.  
can anybody help me with that?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [April 4, 2018, 10:39am UTC](https://forum.inductiveautomation.com/t/historical-machine-status-on-pie-chart/17597/2 "2018-04-04T10:39:58Z")

</div>

Is there a reason to not use a status chart? It would save a boatload of work for you.

---

<div class="post-metadata">

**Author:** ![accyroy](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/accyroy/32/3386_2.png) [@accyroy](https://forum.inductiveautomation.com/u/accyroy)\
**Post date:** [April 4, 2018, 4:15pm UTC](https://forum.inductiveautomation.com/t/historical-machine-status-on-pie-chart/17597/3 "2018-04-04T16:15:30Z")

</div>

Check [this](http://forum.inductiveautomation.com/t/pie-graph-historical-query/15129) thread out.

---

<div class="post-metadata">

**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\
**Post date:** [April 4, 2018, 6:25pm UTC](https://forum.inductiveautomation.com/t/historical-machine-status-on-pie-chart/17597/4 "2018-04-04T18:25:53Z")

</div>

I agree with @JordanCClark. I remember trying to implement my own status tracking using the historian, but it was painful. I eventually decided to store everything myself in a custom schema, with columns for status, start datetime, and end datetime. The current status of any machine is stored in a row with endtime = Null.

When the status tag of a machine changes, a named query is called from the OnValueChanged tag event script. That query is a transaction that closes the previous status by writing its endtime = current time and starting a new status row with starttime = now.

If you think this might be better approach for what you’re trying to do, here are the named queries (with MsSQL syntax):

MachineStatus/LogMachineStatusChange (params: equipment\_id - String, status - Int4):

```auto
SET xact_abort on;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
DECLARE @now datetime = GETDATE()
UPDATE [dbo].[machine_status_log]
	SET
		[end_timestamp] = @now
	WHERE
		[end_timestamp] IS Null
		AND [equipment_id] = :equipment_id;
INSERT INTO [dbo].[machine_status_log] (equipment_id, start_timestamp, end_timestamp, status)
	VALUES (:equipment_id, @now, Null, :status);
COMMIT TRANSACTION;

```

MachineStatus/GetMachineStatusDuration (params: equipment\_id - String, status - Int4, start - datetime, end - datetime):

```auto
SELECT
	SUM(datediff(
        second, 
        CASE WHEN start_timestamp < :start THEN :start else start_timestamp END,
        CASE WHEN end_timestamp IS NULL THEN :end
             WHEN end_timestamp > :end THEN :end else end_timestamp END
        )
    ) AS duration
FROM machine_status_log
WHERE (:start <= start_timestamp OR :start BETWEEN start_timestamp AND isnull(end_timestamp, :end))
	AND (:end >= end_timestamp OR :end BETWEEN start_timestamp AND isnull(end_timestamp, :end))
	AND equipment_id = :equipment_id
	AND status = :status
GROUP BY [status]

```

The case conditions in the SELECT clause and the isnull() conditions in the WHERE clause handle the truncation of statuses that start/end outside of your requested timeframe. For example, if you have a status that runs from 3:25-4:25, but you request all statuses from 3:30-4:00, it will give you that status code with a duration of 30 minutes (in seconds).