# Ignition tag historian - cumulative time spent at value **URL:** \ **Category:** Ignition\ **Created:** [March 15, 2022, 5:30am UTC](https://forum.inductiveautomation.com/t/ignition-tag-historian-cumulative-time-spent-at-value/57188 "2022-03-15T05:30:15Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![eichler-eng](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/eichler-eng/32/21259_2.png) [@eichler-eng](https://forum.inductiveautomation.com/u/eichler-eng)\ **Post date:** [March 15, 2022, 5:30am UTC](https://forum.inductiveautomation.com/t/ignition-tag-historian-cumulative-time-spent-at-value/57188/1 "2022-03-15T05:30:15Z")
Ignition 8.1, MSSQL. Hi. I am using Ignition to monitor the operating status and collect downtime codes from several CNC machines. These integer values are logged by the ignition tag historian on value change. The goal is to calculate OEE and determine the most frequent causes of downtime. For the operating status, I have written a script that loads a snapshot of data from the historian. This is passed through a case select (multiple ifs) which increment a timer for each value of interest. The end result is a summary dataset with the total number of seconds spent in each state (within the time window). This is converted to % in another function. This works OK when there are only a handful of values to scan for. Now I must repeat this for the downtime codes. There are \>10 codes and these are also stored in a SQL table. Is there a less clunky solution using inbuilt functions or the SQL server itself? Some of my code for the CNC machine operating status analysis is below. ```auto def returnStateSeconds(sqlData): # Iterate through the dataset and increment timers for each state. # Declare variables cutting = float(0) idle = float(0) error = float(0) no_data = float(0) # convert to pydataset pyData = system.dataset.toPyDataSet(sqlData) for i in range(len(pyData) - 1): if pyData[i][1] == 0: no_data += system.date.secondsBetween(pyData[i][0], pyData[i+1][0]) elif pyData[i][1] == 1: idle += system.date.secondsBetween(pyData[i][0], pyData[i+1][0]) # Note. State codes 2 (In process) and 3 (OP30) are grouped together into "Cutting". elif pyData[i][1] == 2 or pyData[i][1] == 3: cutting += system.date.secondsBetween(pyData[i][0], pyData[i+1][0]) elif pyData[i][1] == 4: error += system.date.secondsBetween(pyData[i][0], pyData[i+1][0]) utilData = [] # Create an empty list headers = ["state", "seconds"] # Set the dataset headers states = ["Cutting", "Idle", "Error", "No data"] # List of states seconds = [cutting, idle, error, no_data] # List of data for j in range(len(states)): # Add the rows to the list utilData.append([states[j],seconds[j]]) return system.dataset.toDataSet(headers,utilData) # Return the data as a normal dataset ``` Similar questions have been raised in these posts without a full solution. [How to get the total time of an active tag for every hour? - Ignition - Inductive Automation Forum](https://forum.inductiveautomation.com/t/how-to-get-the-total-time-of-an-active-tag-for-every-hour/12455) [Historical tags - Ignition - Inductive Automation Forum](https://forum.inductiveautomation.com/t/historical-tags/11458) Any help would be great. Thanks! ---
**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:** [March 15, 2022, 7:16am UTC](https://forum.inductiveautomation.com/t/ignition-tag-historian-cumulative-time-spent-at-value/57188/2 "2022-03-15T07:16:10Z")
The use of a dictionary can be useful: ```python def returnStateSeconds(sqlData): # Iterate through the dataset and increment timers for each state. # Declare state dict stateDict = { 0 : {'state':'No data'} 1 : {'state':'Idle'} 2 : {'state':'Cutting'} 4 : {'state':'Error'} } # Initialize zero values: for key in stateDict.keys(): stateDict[key]['value'] = 0.0 pyData = system.dataset.toPyDataSet(sqlData) # Process rows for row, nextRow in zip(pyData, pyData[1:]): stateDict[row['value']] =+ system.date.secondsBetween(row['t_stamp'], nextRow['t_stamp']) # create output dataset headers = ["state", "seconds"] data = [[stateDict[key]['state'], stateDict[key]['value']] for key in sorted(stateDict.keys())] return system.dataset.toDataSet(headers, data) ``` If you need the states in a certain order, you can use an ordered dictionary. ```python def returnStateSeconds(sqlData): from collections import OrderedDict # Iterate through the dataset and increment timers for each state. # Declare states as an ordered dictionary stateDict = OrderedDict({ 2 : {'state':'Cutting'} 1 : {'state':'Idle'} 4 : {'state':'Error'}, 0 : {'state':'No data'} }) # Initialize zero values: for key in stateDict.keys(): stateDict[key]['value'] = 0.0 pyData = system.dataset.toPyDataSet(sqlData) # Process rows for row, nextRow in zip(pyData, pyData[1:]): stateDict[row['value']] =+ system.date.secondsBetween(row['t_stamp'], nextRow['t_stamp']) # create output dataset headers = ["state", "seconds"] data = [[stateDict[key]['state'], stateDict[key]['value']] for key in stateDict.keys()] return system.dataset.toDataSet(headers, data) ``` EDIT: If this is just going to be for a downtime pareto, as I’ve read your post more carefully, then you can use [system.dataset.sort()](https://docs.inductiveautomation.com/display/DOC81/system.dataset.sort) before returning.