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

use cases 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 by default, litmus production record stores event items as rows and not individual columns there can be cases where users may want to transpose the default result table to turn event items into columns representing each occurrence of a specific item for this, litmus production record database provides a prepared way method as litmus production record makes use of the eav ( entity attribute value) model for databases, to allow for full flexibility for defining a data model, each data model item returned by the function described in the chapter reading production record event data with utc timestamp docid\ zmrhs1q1rgiydzat6wf2z is an individual row in the result table to show the data in a table where each data model item as well as each additional occurrence is an individual column, the original result table needs to be pivoted to allow users to accomplish this task out of the box, litmus production record database offers the stored procedure prorec prorec getoccurrencebasedtransposedprorecdatautc the stored procedure will create a temporary table which holds the data, against which the user can then run a classic select statement, to allow users to ready the columns they are interested in by default the temporary table is called ##occurrencetransposeprorecdata but can be changed to a custom table name, which is important if for example a dashboard has several panels which are getting data through the stored procedure prorec prorec getoccurrencebasedtransposedprorecdatautc input variables variable name datatype description example @resulttable varchar (50) the stored procedure will store the returned data in a temporary table to allow the user to make use of a select query to only return the columns they are interested in as well as the use of where, order and group by clauses this is the temporary table the user queries against after running the stored procedure it is recommended to always provide a name for the temporary table, so that your query is not impacted by another user temporary tables in microsoft sql are always prefixed with two '#' (example ##occurrencetransposeprorecdata ) therefore if a user does not add two '#', the stored procedure will add the prefix to the name this means that in the select query, the user will have to use the custom name with the two '#' prefix it is possible to not provide a name for the temporary table and use the default value of ##occurrencetransposeprorecdata \##reasoncodelevel2 default @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 26817 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 % testnode 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 @occurance 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 @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 09t13 04 39 647 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 10t13 04 39 647 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) n 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) n 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) n 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) n 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) n 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 @withrecordtimes bit data are stored with the timestamp (recordtime) when they where recorded in litmus production record database when pivoting the result table, users can decide if they want to organize the pivoted table by this recordtime (@withrecordtimes = 1) or by its occurrence (@withrecordtimes = 0) the value can also be default which will assume the value 0 which returns the data based on its occurrence 0 how to use to query transposed production record event data based on occurrence, execute the stored procedure prorec prorec getoccurrencebasedtransposedprorecdatautc first providing all the required inputs first followed by a select statement against the table ##occurrencetransposeprorecdata below is an example using t sql in sql server management studio exec \[le productionrecord] \[dbo] \[ prorec prorec getoccurrencebasedtransposedprorecdatautc] @identifiers = 'asset\ l2 machine1 1 mb,start time 1679656706126', @starttime = '2023 03 24 10 00 00 000' select from ##occurrencetransposeprorecdata; result the function returns a table as result note the occurrence is added to the column name as as suffix example start time 1 > the first occurrence of this event item 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 prorec getoccurrencebasedtransposedprorecdatautc] @identifiers = 'asset\ l2 machine1 1 mb,start time 1679656706126', @starttime = '2023 03 24 10 00 00 000' select prorecid, asset 1,start time 1, end time 1,duration 1 from ##occurrencetransposeprorecdata where level1 is null; this will reduce the number of columns returned to only the three selected it is possible to group the data based on the time they are recorded exec \[le productionrecord] \[dbo] \[prorec prorec getoccurrencebasedtransposedprorecdatautc] @identifiers = 'asset\ l2 machine1 1 mb,start time 1679656706126', @starttime = '2023 03 24 10 00 00 000', @withrecordtimes = 1 select from ##occurrencetransposeprorecdata; the result will be a table, where the data in each column is displayed based on the time they where recorded