3 Replies Latest reply on May 22, 2015 4:21 PM by bshang

    compare times when a tag status changed


      I have a tag which shows the status of an equipment and I am trying to find out how long this tag was in 'Running' state in each month during one year period. I am writing the query using SQL commander and using Asset Framework and trying to use the populated query to create a report in SSRS. For now in the query that I do have I can see the tag value and the time that this tag changes occurred. The question is

      1. Looking for a query to show how long each equipment was in 'Running' state in each month during one year period.

      2. In this case we are dealing with big data and looking for a time efficient solution.

        • Re: compare times when a tag status changed
          Eugene Lee

          Try this: You will have to use PI OLEDB Provider rather than the Enterprise version.


          SELECT *

          FROM [piarchive].[picalc]

          WHERE expr = 'timeeq(''tagname'',''*-1mo'',''*'',"Running")'

            AND time BETWEEN '*-1y' AND '*'

            and timestep = '1mo'

          • Re: compare times when a tag status changed

            Hi Malipour,


            Allow me to divert for a bit:

            This looks like a good use case for PI Event Frames. You can set up AF Analysis to open an event frame when the tag is in "Running" state, and close an event frame when the tag exits the "Running" state. The idea here is that all event frames and their associated duration, attribute values will be stored in the PIFD SQL database (values are "captured" automatically starting in 2.6). Having these information will allow subsequent query and analysis faster if you are dealing with a lot of equipment. Note that this might not be the most direct solution to your situation to find a monthly "running" duration for the equipment, but might be useful if you want to do other analyses on equipment runtime.


            The other approach would be to query for run-time at request, which might be an expensive operation depending on the amount of data (and based on your comment to look for a time efficient solution). If you have a lot of data and do not anticipate these values to change (e.g. no backfilling of data), you might want to pre-compute and store the "running" duration for each month, and then write a query to get those values. Again, you can use AF Analysis to store the "running" duration for each month and write to a PI tag. This approach will be useful if you are going to query the monthly runtime in more than 1 report.


            Either way, what we want to avoid is to have to transfer all the data across the wire and perform the aggregation on the client side.


            Hopefully these ideas can help you start making your design decision. Feel free to provide more information so we can provide additional input.

            • Re: compare times when a tag status changed

              Hi Malipour,


              We'd like to follow up on this thread. Please let us know if you have further questions about the topic, and if not, could you mark one of Eugene or Daphne's post as Correct?