Reading Production Record Event Data for SPC with UTC timestame

use cases returning event 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 prorec spcchartsforprorecitems input variables variable name datatype description example @itemname varchar(50) the name of the item in the data model mandatory! can not be null or empty itemname @nodename varchar(50) the data model root node name mandatory! can not be null or empty testnode @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 @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 2 default @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 @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 @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 how to use to query production record event data for spc, execute the stored procedure prorec prorec spcchartsforprorecitems providing all the required inputs below is an example using t sql in sql server management studio exec \[le productionrecord] \[dbo] \[prorec prorec spcchartsforprorecitems] @itemname = n'faultcode', @nodename = n'downtime', @level1 = n'main classification', @level2 = n'%', @level3 = n'%', @level4 = n'%', @level5 = n'%', @subgroups = 5, @timestart = '2023 03 24t10 33 04z', @timeend= '2023 03 24t16 33 04z', @spcchart = 'rbar' result the function returns a table as result the time is in unix time format