3 Replies Latest reply on Apr 7, 2018 10:50 PM by John Messinger

    Best SQL query to get pitotal data for one single day (Newbie in distress here) ?

    xla99

      Hi,

       

      I need to get daily production data from PI in SQL Server and I’ll have to do that for several tags every day.

       

      I’m trying to see what would be the most efficient query but I’m confused about date functions returning different values (I’m a PI ultra-Newbie).

       

      For example, I need the to know the production for the 2017-01-29  based on the tag  Drg_Production (m3/hour).

       

      I tried the two queries below but they return different values ! What is the difference between the conditions “time BETWEEN '2017-01-29 00:00' AND '2017-01-30 00:00'” and  “time = '2017-01-29 00:00' AND timestep = '1d'” ???

       

      And the most important question, what would be the best query to get my production for the 2017-01-29 ?

       

      Thanks

       

       

      Alex.

       

      SELECT * FROM OPENQUERY(PI,'

      SELECT value * 24 value

      FROM [piarchive]..[pitotal]

      WHERE tag = '' Drg_Production '' AND time BETWEEN ''2017-01-29 00:00'' AND ''2017-01-30 00:00''

      ')

       

      SELECT * FROM OPENQUERY(PI,'

      SELECT value * 24 value

      FROM [piarchive]..[pitotal]

      WHERE tag = '' Drg_Production '' AND time = ''2017-01-29 00:00'' AND timestep = ''1d''

      ')