4 Replies Latest reply on Jun 14, 2018 2:39 PM by vkaufmann

    PI SQL Commander Lite Max Value Per Day Timestamp

    guntonre

      Using PI SQL Commander Lite, I'm using the ft_summarize table under data > tables and I'm fetching the max value for each day starting may 1st. I get the max value, but how do I get to the timestamp for that value?

        • Re: PI SQL Commander Lite Max Value Per Day Timestamp
          vkaufmann

          Hi Richard,

           

          Can you please share your query?

           

          --Vince

          • Re: PI SQL Commander Lite Max Value Per Day Timestamp
            guntonre

            I get a Time field, but its just the date, no time.

             

            1036MVA5.783825397491465/2/2018 12:00:00 AM
            1036MVA4.706202625566065/3/2018 12:00:00 AM
            1036MVA5.810867786407475/4/2018 12:00:00 AM
            1036MVA5.737770557403565/5/2018 12:00:00 AM
            • Re: PI SQL Commander Lite Max Value Per Day Timestamp
              guntonre

              SELECT eh.Name Element, ea.Name Attribute, s.Value, s.Time

              FROM [Default].[Asset].[ElementHierarchy] eh,

              (

                   SELECT N'Maximum' SummaryType

              ) t

              INNER JOIN [Default].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,

              [Default].[Data].[ft_Summarize] s

              WHERE eh.Path LIKE N'\Circuits\%'

                   AND s.ElementAttributeID = ea.ID

                   AND s.StartTime = N'5/01/2018'

                   AND s.EndTime = N't'

                   AND s.TimeStep = N'1d'

                   AND s.SummaryType = t.SummaryType

                   AND s.CalculationBasis = N'TimeWeighted'

                   AND s.TimeType = N'MostRecentTime'

                   AND ea.Name = 'MVA'

                   AND eh.Name = '1036'

              OPTION (FORCE ORDER, EMBED ERRORS)

                • Re: PI SQL Commander Lite Max Value Per Day Timestamp
                  vkaufmann

                  Hi Richard,

                  Update: It seems if you change the TimeType parameter to 'Auto' instead of 'MostRecentTime' and include the Time column in your query everything is happy. I verified in OLEDB Ent and RTQP.

                   

                  I took some time to look into this and found that the Summarize function table doesn't correctly display the timestamp of the Min/Max of the sampled summary. I have informed the development team of this oversight and have created a bug item to address. The behavior is consistent in the upcoming release of RTQP which is meant to replace OLEDB Enterprise entirely so the first place to look for a fix would be there.

                   

                  There is a potential workaround using PI OLEDB Classic Provider in that the PIMAX table exposes a "timeofmax" column that provides precisely what you are looking for. The downside is that turns your query into something tag-based and not asset-based.

                   

                  --Vince

                  2 of 2 people found this helpful