Reading Tag Data for SPC with UTC timestame

5min

Use Cases
Returning tag data recorded by Litmus Production Record Database for SPC analytics through the use of these SELECT statements can be used for example by any visualization platform which allows to integrate with MSSQL like;
Grafana 
PowerBI
Tableau
Further can these queries be used by any other application which allows for an ODBC connection such as:
Excel
C#
.Net
Python
R
They can also be part of a larger stored procedure developed by users with T-SQL.
Method
Execute Stored Procedure PROREC_Tag_SPCChartsForTags
Input Variables
Variable name
Datatype
Description
Example
@TagItem
VARCHAR(50)
The item of the tag which the value represents.
﻿
Mandatory!
Can not be NULL or Empty
Value
﻿
﻿
﻿
@TagName
VARCHAR(50)
The Tag name.

Mandatory!
Can not be NULL or Empty
Pressure
﻿
﻿
﻿
@DeviceName
VARCHAR(50)
The device name for the tag.
﻿
Mandatory!
Can not be NULL or Empty
Asset1
﻿
﻿
﻿
@TimeSTART
DATETIME
The start of the time period for which to query data.
﻿
If no specific start time is required, the value can also be DEFAULT which will assume the value null.

This will trigger the function to calculate a start time of -1h from the current time stamp.
﻿
This means the default time period for which data are returned is always the last hour.
2023-03-09T13:04:39.647
﻿
﻿
﻿
DEFAULT
@TimeEND
DATETIME
The end of the time period for which to query data.
﻿
If no specific end time is required, the value can also be DEFAULT which will assume the value null.

This will trigger the function to use the current time stamp.
﻿
This means the default time period for which data are returned is always the last hour.
2023-03-10T13:04:39.647
﻿
﻿
﻿
DEFAULT
@subgroups
INT
The subgroup size.
﻿
Subgroups consist of individual measurements or readings and the number of measurements or readings is referred to as the subgroup size

Values can range between 2 and 25
﻿
A common subgroup size is 5.

Mandatory!
Can not be NULL or Empty
5
@spcchart
VARCHAR(10)
Is the type of the SPC chart.

The available options are:
xbar
rbar
sbar
ibar
mrbar
﻿
Mandatory!
Can not be NULL or Empty
rbar
How to use
To query production record event data for SPC, execute the stored procedure PROREC_Tag_SPCChartsForTags providing all the required inputs.
Below is an example using T-SQL in SQL Server Management Studio:
EXEC [LE_ProductionRecord].[dbo].[PROREC_Tag_SPCChartsForTags] 
    @TagItem = N'Value', 
    @TagName = N'ToolCtr_Axis_X',
    @DeviceName = N'ToolCtr_V1_3BSM_Makino1',
    @subgroups = 5, 
    @TimeSTART = '2023-03-24T16:46:00Z', 
    @TimeEND= '2023-03-24T16:51:00Z',
    @spcchart = 'rbar'
Result
The function returns a table as result. The Time is in UNIX time format.
tag data for SPC utc
﻿
 

Variable name	Datatype	Description	Example
@TagItem	VARCHAR(50)	The item of the tag which the value represents. Mandatory! Can not be NULL or Empty	Value
@TagName	VARCHAR(50)	The Tag name. Mandatory! Can not be NULL or Empty	Pressure
@DeviceName	VARCHAR(50)	The device name for the tag. Mandatory! Can not be NULL or Empty	Asset1
@TimeSTART	DATETIME	The start of the time period for which to query data. If no specific start time is required, the value can also be DEFAULT which will assume the value null. This will trigger the function to calculate a start time of -1h from the current time stamp. This means the default time period for which data are returned is always the last hour.	2023-03-09T13:04:39.647 DEFAULT
@TimeEND	DATETIME	The end of the time period for which to query data. If no specific end time is required, the value can also be DEFAULT which will assume the value null. This will trigger the function to use the current time stamp. This means the default time period for which data are returned is always the last hour.	2023-03-10T13:04:39.647 DEFAULT
@subgroups	INT	The subgroup size. Subgroups consist of individual measurements or readings and the number of measurements or readings is referred to as the subgroup size Values can range between 2 and 25 A common subgroup size is 5. Mandatory! Can not be NULL or Empty	5
@spcchart	VARCHAR(10)	Is the type of the SPC chart. The available options are: xbar rbar sbar ibar mrbar Mandatory! Can not be NULL or Empty	rbar

Updated 29 Sep 2023

Reading Production Record Event Data for SPC with UTC timestame

Reading out the Log Table for the asynchronous background query execution