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

Hello

 

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

 

 

Tom

Outcomes