Reading Tag Data Transposed with UTC timestamp

7min

Use Cases
Returning tag data recorded by Litmus Production Record Database 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.
By default, Litmus Production Record stores Tag items as rows and not individual columns.
There can be cases where users may want to transpose the default result table to turn Tag Items into columns.
For this, Litmus Production Record Database provides a prepared way.
Method
Run SELECT query against the table ##TransposeTagData after executing the stored procedure PROREC_Tag_GetTransposedTagDataUTC
Input Variables
Variable name
Datatype
Description
Example
@Tag
VARCHAR(50)
The Tag name.

If no specific Tag name is to be queried or to get a list of Tag name's, the value can also be DEFAULT which will assume the value %.
Pressure
﻿
﻿
DEFAULT
@Device
VARCHAR(50)
The device name for the tag.
﻿
If no specific device is to be queried or to get a list of devices, the value can also be DEFAULT which will assume the value %.
Asset1
﻿
﻿
DEFAULT
@Item
VARCHAR(50)
The item of the tag which the value represents.
﻿
If no specific item is to be queried or to get a list of item's, the value can also be DEFAULT which will assume the value %.
Value
﻿
﻿
﻿
DEFAULT
@TagValue
VARCHAR(256)
A specific value for the Item of the Tag.
﻿
If no specific Value for the Item is to be queried or to return all Values, the value can also be DEFAULT which will assume the value %.
20
﻿
﻿
﻿
DEFAULT
@Quality
VARCHAR(4)
A specific quality of the tag value. (GOOD or BAD)
﻿
If no specific quality for the value is to be queried or to return all qualities, the value can also be DEFAULT which will assume the value %.
GOOD
﻿
﻿
﻿
DEFAULT
@StartTime
DATETIME
The start of the time period for which to query data.
﻿
The time will be treated as UTC.
﻿
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-09 12:57:59.200
﻿
﻿
﻿
﻿
﻿
﻿
DEFAULT
@EndTime
DATETIME
The end of the time period for which to query data.
﻿
The time will be treated as UTC.
﻿
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-10 12:57:59.200
﻿
﻿
﻿
﻿
﻿
﻿
DEFAULT
How to use
To query transposed tag data, execute the stored procedure PROREC_Tag_GetTransposedTagDataUTC first providing all the required inputs first. Followed by a SELECT statement against the table ##TransposeTagData.
Below is an example using T-SQL in SQL Server Management Studio:
exec [LE_ProductionRecord].[dbo].[PROREC_Tag_GetTransposedTagDataUTC] 
select * from ##TransposeTagData;
Result
The function returns a table as result.
read transposed tag data utc
﻿
Additional Options
It is possible to reduce the number of columns returned by the SELECT statement.
Below is an example using T-SQL in SQL Server Management Studio:
exec [LE_ProductionRecord].[dbo].[PROREC_Tag_GetTransposedTagDataUTC] select Tag, Value from ##TransposeTagData;
This will reduce the number of columns returned to only the two selected.
Reduced result set transposed tag data UTC
﻿
﻿

Variable name	Datatype	Description	Example
@Tag	VARCHAR(50)	The Tag name. If no specific Tag name is to be queried or to get a list of Tag name's, the value can also be DEFAULT which will assume the value %.	Pressure DEFAULT
@Device	VARCHAR(50)	The device name for the tag. If no specific device is to be queried or to get a list of devices, the value can also be DEFAULT which will assume the value %.	Asset1 DEFAULT
@Item	VARCHAR(50)	The item of the tag which the value represents. If no specific item is to be queried or to get a list of item's, the value can also be DEFAULT which will assume the value %.	Value DEFAULT
@TagValue	VARCHAR(256)	A specific value for the Item of the Tag. If no specific Value for the Item is to be queried or to return all Values, the value can also be DEFAULT which will assume the value %.	20 DEFAULT
@Quality	VARCHAR(4)	A specific quality of the tag value. (GOOD or BAD) If no specific quality for the value is to be queried or to return all qualities, the value can also be DEFAULT which will assume the value %.	GOOD DEFAULT
@StartTime	DATETIME	The start of the time period for which to query data. The time will be treated as UTC. 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-09 12:57:59.200 DEFAULT
@EndTime	DATETIME	The end of the time period for which to query data. The time will be treated as UTC. 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-10 12:57:59.200 DEFAULT

Updated 29 Sep 2023

Reading Production Record Event Data Transposed based on Occurrence with UTC timestamp

Reading Tag Meta Data Transposed with UTC timestamp