AnsweredAssumed Answered

Average value over 7 days for a particular time window; PI SQL Commander

Question asked by tcrumbleholme on Sep 18, 2015
Latest reply on Sep 18, 2015 by czhang



I have had a request made as follows:


What is average value for a tag between 0300 and 0400 on a weekly basis.


What they mean is retrieve every value for the tag between 0300 and 0400 over the course of a week and give me the average; they also want it every week for the past 6 months.


I have been unable to find an elegant solution and as such am using the following:


SELECT tag, time, value

FROM [piarchive].[picomp2]

WHERE tag = 'sinusoid'

     AND time between '*-180d' AND '*'

     AND HOUR(time) >= 3

     AND HOUR(time) <4


This produced four values per day over the 180 days.  I am then putting it into excel to get the week number then pivoting the data grouped by Week Number and averaging the value.


I have to do this on over 7000 points.


Is there a better way?


Many thanks