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 166,112,225,219,133,114 false true false unhandled content type unhandled content type unhandled content type unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type unhandled content type false unhandled content type unhandled content type unhandled content type unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type 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