Reading Production Record Event Data with Local timestamp
Returning event 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.
Run SELECT query with Function PROREC_ProRec_GetProRecDataLocalTime as FROM clause.
Variable name | Datatype | Description | Example |
|---|---|---|---|
@ProRecID | BIGINT | A specific internal Production Record ID. If no specific Production Record ID is to be queried or to get a list of Production Record ID's, the value can also be DEFAULT which will assume the value null. | 26811 DEFAULT |
@Identifiers | VARCHAR (8000) | A key-value pair or comma separated list of key-value pairs for know Identifiers which describe a Production Record. If no specific Identifiers are to be queried or to get a list of Identifiers 's, the value can also be DEFAULT which will assume the value %. | Asset:L2_Machine1_1_MB,START_TIME:1678295211950 DEFAULT |
@NodeName | VARCHAR (50) | The Data Model root Node name. If no specific NodeName is to be queried or to get a list of NodeName's, the value can also be DEFAULT which will assume the value %. | TestArea DEFAULT |
@Level1 | VARCHAR (50) | The name of a the first Sub Level in the Data Model (if defined). If no specific Level1 is to be queried or to get a list of Level1's, the value can also be DEFAULT which will assume the value %. | TestLevel1 DEFAULT |
@Level2 | VARCHAR (50) | The name of the second Sub Level in the Data Model (if defined). If no specific Level2 is to be queried or to get a list of Level2's, the value can also be DEFAULT which will assume the value %. | TestLevel2 DEFAULT |
@Level3 | VARCHAR (50) | The name of the third Sub Level in the Data Model (if defined). If no specific Level3 is to be queried or to get a list of Level3's, the value can also be DEFAULT which will assume the value %. | TestLevel3 DEFAULT |
@Level4 | VARCHAR (50) | The name of the fourth Sub Level in the Data Model (if defined). If no specific Level4 is to be queried or to get a list of Level4's, the value can also be DEFAULT which will assume the value %. | TestLevel4 DEFAULT |
@Level5 | VARCHAR (50) | The name of the fifth Sub Level in the Data Model (if defined). If no specific Level5 is to be queried or to get a list of Level5's, the value can also be DEFAULT which will assume the value %. | TestLevel5 DEFAULT |
@ItemName | VARCHAR (50) | The name of the Item in the Data Model. 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 %. | ItemName DEFAULT |
@ItemValue | VARCHAR (256) | A specific value for the Item in the Data Model. 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 |
@Eng_Unit | VARCHAR (20) | A specific engineering Unit for the Item in the Data Model (if defined). If no specific Engineering Unit for the Item is to be queried or to return all engineering units if defined, the value can also be DEFAULT which will assume the value %. | Kg DEFAULT |
@Occurrence | INTEGER | A specific occurrence of the Item in the Data Model. If no specific occurrence for the Item is to be queried or to return all occurrence's, the value can also be DEFAULT which will assume the value null. This can be useful if for example an operation can run multiple times throughout a process. | 1 DEFAULT |
@StartTime | 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-09 12:57:59.200 DEFAULT |
@EndTime | 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-10 12:57:59.200 DEFAULT |
@StartTimeItem | VARCHAR (50) | The Function will try to calculate the overall Duration of each recorded events and sub step included in the event. To do so, it requires to know what is the Item which does symbolize the start of the event. If users have followed the Litmus recommendations and used "START_TIME", the value can be set as DEFAULT. Otherwise, the user needs to provide the respective Item. If the Item does not exist or was not recorded, the Duration will not calculated and returned. | StartTime DEFAULT |
@EndTimeItem | VARCHAR (50) | The Function will try to calculate the overall Duration of each recorded events and sub step included in the event. To do so, it requires to know what is the Item which does symbolize the end of the event or sub step. If users have followed the Litmus recommendations and used "END_TIME", the value can be set as DEFAULT. Otherwise, the user needs to provide the respective Item. If the Item does not exist or was not recorded, the Duration will not calculated and returned. | EndTime DEFAULT |
@FilterItemName1 | VARCHAR (256) | In cases where results are to be filtered additionally to the Identifiers, for example based on the value of a specific item, the function allows to provide up to 5 additional filters for this purpose. This can be understood similar to the use of where clauses, but reduces the complexity for users. To use the filter, the user has to provide the hierarchy and item name as a comma-separated string followed by the item value separated by a double point. If no specific Filter is to be used the value can also be DEFAULT which will assume the value % which applies no filter. | 'Production,Asset,Step1,Site:LA' DEFAULT |
@FilterItemName2 | VARCHAR (256) | In cases where results are to be filtered additionally to the Identifiers, for example based on the value of a specific item, the function allows to provide up to 5 additional filters for this purpose. This can be understood similar to the use of where clauses, but reduces the complexity for users. To use the filter, the user has to provide the hierarchy and item name as a comma-separated string followed by the item value separated by a double point. If no specific Filter is to be used the value can also be DEFAULT which will assume the value % which applies no filter. | 'Production,Asset,Step1,Plant:Plant1' DEFAULT |
@FilterItemName3 | VARCHAR (256) | In cases where results are to be filtered additionally to the Identifiers, for example based on the value of a specific item, the function allows to provide up to 5 additional filters for this purpose. This can be understood similar to the use of where clauses, but reduces the complexity for users. To use the filter, the user has to provide the hierarchy and item name as a comma-separated string followed by the item value separated by a double point. If no specific Filter is to be used the value can also be DEFAULT which will assume the value % which applies no filter. | 'Production,Asset,Step1,Shift:2' DEFAULT |
@FilterItemName4 | VARCHAR (256) | In cases where results are to be filtered additionally to the Identifiers, for example based on the value of a specific item, the function allows to provide up to 5 additional filters for this purpose. This can be understood similar to the use of where clauses, but reduces the complexity for users. To use the filter, the user has to provide the hierarchy and item name as a comma-separated string followed by the item value separated by a double point. If no specific Filter is to be used the value can also be DEFAULT which will assume the value % which applies no filter. | 'Production,Asset,Step1,Line:1' DEFAULT |
@FilterItemName5 | VARCHAR (256) | In cases where results are to be filtered additionally to the Identifiers, for example based on the value of a specific item, the function allows to provide up to 5 additional filters for this purpose. This can be understood similar to the use of where clauses, but reduces the complexity for users. To use the filter, the user has to provide the hierarchy and item name as a comma-separated string followed by the item value separated by a double point. If no specific Filter is to be used the value can also be DEFAULT which will assume the value % which applies no filter. | 'Production,Asset,Step1,Asset:Machine1' DEFAULT |
@Timezone | INTEGER | The Number of hours your local time zone is ahead or behind the GMT time zone. If the user does not know this value, it can be left as DEFAULT which will assume the value 0 or GMT. The value can be positive (example: 8 for UTC +8) or negative (example: -6 for UTC -6) | 1 DEFAULT |
To query production record event data, execute a SELECT statement from the function PROREC_ProRec_GetProRecDataUTC providing all the required inputs. Below is an example using T-SQL in SQL Server Management Studio:
The function returns a table as result.
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:
This will reduce the number of columns returned to only the three selected.
It is possible to sort data based on users requirements returned by the SELECT statement. Below is an example using T-SQL in SQL Server Management Studio:
The result will be a table, where all rows are first sorted by their Recordtime in descending order and then by their ItemName in ascending alphabetical Order.
It is possible to filter data based on the value of a different specific item, which is not to be part of the result list.
In this example, we want the values of all Part numbers when the value of the item Asset is "Machine1".
SELECT * FROM [dbo].[PROREC_ProRec_GetProRecDataLocalTime] ( DEFAULT ,DEFAULT ,'Production' ,DEFAULT ,DEFAULT ,DEFAULT ,DEFAULT ,DEFAULT ,'PartNo' ,DEFAULT ,DEFAULT ,DEFAULT ,'2023-07-03T11:29:04Z' ,'2023-07-10T11:29:04Z' ,DEFAULT ,DEFAULT ,N'Production,Asset,Asset:Machine1' ,DEFAULT ,DEFAULT ,DEFAULT ,DEFAULT ,1)
The result will be a table, with the filtered data.
