11 Replies Latest reply on Jan 24, 2017 10:51 AM by gregor

    SQL event if value changed

    igreguri

      Hi,

      I need to monitor control loops if value changed (in shift, 12 hours).

      When value was changed from AUTO or CAS then show time, tagname, old value and new value.

      Time must be from 7 AM to 7 PM and after that from 7 PM to 7 AM.

      Is possible to write this changes to one PI point (PS: I have older AF v2.5.0.5039 without Analyses)?

       

      My query:

      SELECT format (time,'dd.MM.yyyy  hh:mm') as time, s.tag, value as now, prevval(s.tag, time) beforeval, prevevent(s.tag, time) beforetime

      FROM [piarchive].[pisnapshot] s

      inner JOIN [pipoint].[classic] d ON s.tag = d.tag

      WHERE s.tag LIKE 'RADU3LC%.MODE' or s.tag LIKE 'RADU3PC%.MODE' or s.tag LIKE 'RADU3TC%.MODE' or s.tag LIKE 'RADU3FC%.MODE' AND value <> prevval(s.tag, time)

       

      query3.jpg

      Thanks,

      Igor

        • Re: SQL event if value changed
          gregor

          Hello Igor,

           

          It's possible to insert into picomp2 table. Please have a look at this other discussion.

          I suggest you adjust your query to have the columns tag, time, value with tag equal to the destination tag.

          2 of 2 people found this helpful
            • Re: SQL event if value changed
              igreguri

              Thanks again Gregor, I'll test this!

              Can you help me first with sintax in query...

              Condition 1: How to show only tagnames which was changed from previous value (AUTO or CAS) to value MAN.

              and condition 2: If now time is > 7 AM and < 7 PM then values must be from 7 AM to 7 PM, or if now  time is > 7 PM (day before) and < 7 AM (today) then values must be from 7 PM to 7 AM.

               

              I try with this query for only first condition but message... Timeout has expired.

              SELECT format (time,'dd.MM.yyyy  hh:mm') as time, s.tag, value as now, prevval(s.tag, time) beforeval, prevevent(s.tag, time) beforetime

              FROM [piarchive].[pisnapshot] s

              inner JOIN [pipoint].[classic] d ON s.tag = d.tag

              WHERE s.tag LIKE 'RADU3LC%.MODE' or s.tag LIKE 'RADU3PC%.MODE' or s.tag LIKE 'RADU3TC%.MODE' or s.tag LIKE 'RADU3FC%.MODE' AND value = 'MAN' and prevval(s.tag, time) = 'AUTO' or prevval(s.tag, time) = 'CAS'

                • Re: SQL event if value changed
                  gregor

                  Hello Igor,

                   

                  Please accept my apologies. I wasn't looking at the details of your SELECT statement because I understood it is delivering the expected results already.

                  Below please find an example how you can evaluate on the previous state (Condition 1) and an example how you can identify the shift you are currently in (Condition 2).

                  To be able to help you beyond this, we need to get a better understanding of what you like to accomplish. Can you please elaborate a bit? Please also try to answer the following questions:

                   

                  What's your data density? How many value changes per raw data point do you see within a shift?

                  How would you like to handle the 12 hours shift? When do you like to execute the query and how should result differ depending on the shift?

                  Do you need to consider all value changes within a shift?

                  I understand that you are having multiple control loops. By writing all calculation results to a single PI Point, you would loose the context of the control loop. Are you planning to use one output tag per control loop? Is my assumption correct that you are looking at a single raw data tag per control loop?

                   

                  You mention that you run an older AF Server version without Analysis. Would updating your AF Server and using Asset Analytics be an option for you? If not, have you considered using PI Performance Equations? While it is possible using PI OLEDB Provider (classic) to perform calculations and to write results back to PI Points, this is likely not the easiest approach because queries easily become very complex.

                   

                  SELECT * FROM 
                  (
                      SELECT time, s.tag, DIGSTRING(CAST(value AS Int32)) as now, prevval(s.tag, time) beforeval, prevevent(s.tag, time) beforetime
                      FROM [piarchive].[pisnapshot] s
                      WHERE s.tag LIKE 'CDM15%'
                  ) t1
                  WHERE now <> beforeval 
                      AND now = 'CASCADE' 
                      AND beforeval = 'AUTO'
                  

                   

                  SELECT 
                      CASE 
                          WHEN DATE('*') BETWEEN 't+7h' AND 't+19h'
                          THEN 'Shift 1'
                          WHEN DATE('*') BETWEEN 'y+19h' AND 't+7h'
                          THEN 'Shift 2'
                          ELSE
                          'No shift' -- This should never happen
                      END
                  
                    • Re: SQL event if value changed
                      igreguri

                      Hi Gregor,

                      I think, contition 2 is not so important (to simplify). Is enough to show data for changed statuses for current day.
                      Condition 1 example:

                      CL Status example.jpg

                      I have about 100 records in month for one control loop (of 1100).

                      I try to write code down, but problem is prevval because in this case value 'MAN' is repeated and I just like to see last change from CAS, AUTO or MAN to diferent state.

                       

                      SELECT format (time,'dd.MM.yyyy  hh:mm') as time, s.tag, prevval(s.tag, time) beforeval, value as now

                      FROM [piarchive].[pisnapshot] s

                      inner JOIN [pipoint].[classic] d ON s.tag = d.tag

                      WHERE s.tag LIKE 'RADU3%MODE'  and value = 'MAN' and prevval(s.tag, '*-20d') <> 'MAN'

                       

                      CL Status example 2.jpg

                      I hope that is more clearly.

                      Regards,

                      Igor

                        • Re: SQL event if value changed
                          gregor

                          Hello Igor,

                           

                          Sorry, but you are leaving some important questions unanswered. Please also note that prevval(s.tag '*-20d') refers the value before now minus 20 days and not the value before the current value.

                          Please allow me to suggest you forget about the solution for a while and describe what you like to accomplish. When doing this, please keep it as simple as possible e.g. by looking at a single control loop for now.

                          Please allow me to repeat the questions.

                           

                          1. What's your data density? How many value changes per raw data point do you see within a shift?
                            You answered this one.
                          2. How would you like to handle the 12 hours shift? When do you like to execute the query and how should result differ depending on the shift?
                            You answered this one too but at what times do you like to report results? Once every day at midnight or continuously?
                          3. Do you need to consider all value changes within a shift?
                            This question is kind of related to the previous one. Under the assumption, you like to evaluate only once a day at midnight, do you like to look at just the 2 latest values or make the analysis over the course of the day?
                          4. Are you planning to use one output tag per control loop? Is my assumption correct that you are looking at a single raw data tag per control loop?
                            From your reply, I understand that you have about 1,100 control loops with one raw data tag. Is my assumption correct that you like to report the results with the same amount of result tags?

                           

                          In addition to the above please let us know if updating your AF Server to allow you using Asset Analytics is an option.

                          What about using PI Performance Equations?

                          1 of 1 people found this helpful
                            • Re: SQL event if value changed
                              igreguri

                              Hi Gregor,

                              sorry if if I was not clear before and thaks for help...

                               

                              2. I’d like to see results continuosly (for ex. in relational data sheet). If we want to see changes in shifts, then must be...

                              Examples:

                              • There are two shifts, 07-19 (daily shift, 24h time format), and 19-07 hours (night shift).

                              If is now 13 hour, then I’d like to see results from todays shift 07-19 (in this case 07-13h).

                              If is now 23 hour, then query results must be 19-23h

                              If is now 02 hour, then query results must be from 19-00 (day before) and 00-02 (today)

                               

                              3. I’d like to consider all values within shift, from the beginning of the shift to curently time during the shift. We want to monitoring changes for all control loops realtime.

                              4. I’d like to write control loops names for each of this status changes to one PI point. If I have time and control loop name, I can find later (if is neaded) status changes.

                              Example:

                              PI tag name: CL_Changes.PV

                              Timestamp: 13/01/2017 09:00:00

                              Value (CL name): RADU3FC13.MODE

                               

                              This function can be separately, I can put this to some interface which would do that periodically. Do you have any suggestions?

                                • Re: SQL event if value changed
                                  gregor

                                  Hello Igor,

                                   

                                  We have a sample query for you which you based on CDM158. Please adjust the WHERE clause to refer your raw data tags and for the usage with your statuses.

                                   

                                  You can adjust the following line by replacing the now ('*') reference with your end of shift.

                                   

                                              SELECT DATE('*') endtime --here you can adjust time of execution (just for testing)
                                  

                                   

                                  Please let us know if this query delivers what you need and if you need additional guidance on inserting the results to your point CL_Changes.PV

                                   

                                  SELECT *
                                  FROM
                                  (
                                      SELECT time, tag, DIGSTRING(CAST(value AS Int32)) as now, prevval(tag, time) beforeval
                                      FROM
                                      (
                                          SELECT 
                                              CASE
                                                  WHEN endtime BETWEEN DATE('t+7h') AND DATE('t+19h')
                                                  THEN DATE('t+7h')
                                                  WHEN endtime BETWEEN DATE('t+17h') AND DATE('t+24h')
                                                  THEN DATE('t+19h')
                                                  ELSE DATE('y+19h')
                                              END starttime,
                                              endtime
                                          FROM
                                          (
                                              SELECT DATE('*') endtime --here you can adjust time of execution (just for testing)
                                          ) t1
                                      ) t2,
                                      piarchive..picomp2
                                      WHERE tag = 'CDM158'
                                          AND time BETWEEN starttime AND endtime
                                  ) t3
                                  WHERE now <> beforeval 
                                      AND beforeval IN ('Auto', 'Cascade', 'Manual')
                                  OPTION (FORCE ORDER)
                                  
                                  2 of 2 people found this helpful
                                    • Re: SQL event if value changed
                                      igreguri

                                      Hi Gregor, with small corrections this query works perfect, thanks!

                                      I must correct this DIGSTRING(CAST(value AS Int32)) as now because did not work in my case. Probably because my *.MODE points are STRING and CDM158 is Digital.

                                      SELECT format (time,'dd.MM.yyyy  HH:mm:ss') as time, tag, value as now, prevval(tag, time) beforeval

                                      I was also corrected... to see all changes.

                                      WHERE now <> beforeval  
                                      --AND beforeval IN ('AUTO', 'CAS', 'MAN', 'BAD')

                                      CL Status example 3.jpg

                                      You ask me about Performance Equations... Is it possible using this to write changes into PI point this query results, and how?

                                      In this case will be better to use two points. One for tag name (ex. CL_ChangesTag.PV) and second for now value (ex. CL_ChangesStatus.PV) with same timestamp, for comparison.

                                      Regards,

                                      Igor

                                        • Re: SQL event if value changed
                                          gregor

                                          Hello Igor,

                                           

                                          It's recommended using Digital data type instead of String data types where it makes sense because the longer strings become, the more expensive it is to store them compared to what it takes to store just the 2 byte integer representing a Digital State. With the short strings you are using, the savings would be minimal if at all.

                                           

                                          When inserting into picomp2 table, you need to provide at least tag, time and value. For this reason, please see the modified version of the query provided earlier.

                                           

                                          SELECT 'CDM158Tag.PV' as tag, t3.time, CONCAT(t3.tag, '_', CAST(t3.beforeval as String), ' -> ', t3.now) as value
                                          FROM
                                          (
                                              SELECT picomp2.time, picomp2.tag, DIGSTRING(CAST(picomp2.value AS Int32)) as now, prevval(picomp2.tag, time) as beforeval--, prevevent(tag, time) beforetime 
                                              FROM
                                              (
                                                  SELECT 
                                                      CASE
                                                          WHEN endtime BETWEEN DATE('t+7h') AND DATE('t+19h')
                                                          THEN DATE('t+7h')
                                                          WHEN endtime BETWEEN DATE('t+17h') AND DATE('t+24h')
                                                          THEN DATE('t+19h')
                                                          ELSE DATE('y+19h')
                                                      END starttime,
                                                      endtime
                                                  FROM
                                                  (
                                                      SELECT DATE('*') endtime --here you can adjust time of execution (just for testing)
                                                  ) t1
                                              ) t2,
                                              piarchive..picomp2
                                              WHERE picomp2.tag LIKE 'CDM158%'
                                                  AND picomp2.time BETWEEN starttime AND endtime
                                          ) t3
                                          WHERE now <> beforeval 
                                              AND beforeval IN ('Auto', 'Cascade', 'Manual')
                                          

                                           

                                          Please note that the value requires a string because it has the source tag name, the previous and the actual value. Please modify the query as needed and make sure the results look good before you try inserting them to picomp2.

                                           

                                          INSERT INTO piarchive..picomp2 (tag, time, value)
                                          SELECT 'CDM158Tag.PV' as tag, t3.time, CONCAT(t3.tag, '_', CAST(t3.beforeval as String), ' -> ', t3.now) as value
                                          FROM
                                          (
                                              SELECT picomp2.time, picomp2.tag, DIGSTRING(CAST(picomp2.value AS Int32)) as now, prevval(picomp2.tag, time) as beforeval--, prevevent(tag, time) beforetime 
                                              FROM
                                              (
                                                  SELECT 
                                                      CASE
                                                          WHEN endtime BETWEEN DATE('t+7h') AND DATE('t+19h')
                                                          THEN DATE('t+7h')
                                                          WHEN endtime BETWEEN DATE('t+17h') AND DATE('t+24h')
                                                          THEN DATE('t+19h')
                                                          ELSE DATE('y+19h')
                                                      END starttime,
                                                      endtime
                                                  FROM
                                                  (
                                                      SELECT DATE('*') endtime --here you can adjust time of execution (just for testing)
                                                  ) t1
                                              ) t2,
                                              piarchive..picomp2
                                              WHERE picomp2.tag LIKE 'CDM158%'
                                                  AND picomp2.time BETWEEN starttime AND endtime
                                          ) t3
                                          WHERE now <> beforeval 
                                              AND beforeval IN ('Auto', 'Cascade', 'Manual')
                                          

                                           

                                          Below is a sample query for your Status.pv tag which mainly looks at the snapshot table and compares against the previous value.

                                           

                                          INSERT INTO piarchive..picomp2 (tag, time, value)
                                          SELECT 'CDM158Status.PV' as tag, t3.time, CONCAT(t3.tag, '_', CAST(t3.beforeval as String), ' -> ', t3.now) as value
                                          FROM
                                          (
                                              SELECT pisnapshot.time, pisnapshot.tag, DIGSTRING(CAST(pisnapshot.value AS Int32)) as now, prevval(pisnapshot.tag, time) as beforeval--, prevevent(tag, time) beforetime 
                                              FROM
                                              (
                                                  SELECT 
                                                      CASE
                                                          WHEN endtime BETWEEN DATE('t+7h') AND DATE('t+19h')
                                                          THEN DATE('t+7h')
                                                          WHEN endtime BETWEEN DATE('t+17h') AND DATE('t+24h')
                                                          THEN DATE('t+19h')
                                                          ELSE DATE('y+19h')
                                                      END starttime,
                                                      endtime
                                                  FROM
                                                  (
                                                      SELECT DATE('*') endtime --here you can adjust time of execution (just for testing)
                                                  ) t1
                                              ) t2,
                                              piarchive..pisnapshot
                                              WHERE pisnapshot.tag LIKE 'CDM158%'
                                          ) t3
                                          WHERE now <> beforeval 
                                              AND beforeval IN ('Auto', 'Cascade', 'Manual')
                                          
                                          2 of 2 people found this helpful
                                            • Re: SQL event if value changed
                                              igreguri

                                              Hi Gregor,

                                              I tested this queryes with small modifications and works fine, thanks!

                                              CL Status example 4.jpg

                                              Now I have to do some VB application (PI SDK) to frequently update my CL_ChangesStatus.PV point with values from snapshot table, like interface.

                                              What do you suggest, how to trigger this updating?

                                                • Re: SQL event if value changed
                                                  gregor

                                                  Hello Igor,

                                                   

                                                  If you agree that we've answered your question, please click the [Correct Answer] button on the reply that comes closest to what you've been looking for.

                                                   

                                                  If you have additional questions, please consider creating a new question. There's no limit on the amount of questions you can ask nor do we invoice extra charges for additional questions If a question is related to another question, you can refer the other question by linking it in your new question.

                                                  Would you agree to branch into a new question?

                                                   

                                                  I understood that you like to service CL_ChangesStatus.PV with the second insert statement you've asked for. Can you please explain what you like to accomplish?

                                                  PI SDK is announced for deprecation and hence I would suggest using AF SDK instead. I understand your preferred language is VB.NET. Please confirm.

                                                  Can you please describe what the application you like to develop should do?