Hourly averages and end of the day summary

Archived Topics
1

I’m using FSQL to logging data every minute into a SQL 2000 DB. I would like to create hourly averages and end of the day summary data from the data stored in the one minute table. I have t_stamp, _DateTimeLocal and _TimeMinutes (from Kepware) logging in the table. I guess that I need to trigger a stored procedure but I’m not sure how to do that.

2

I dont know if this will help but we are using two methods one which I found on this site in another forum posting and one in which Nathan from IA helped me with. Feel free to choose which one works best for you.

In FSQL which is what is logging the data you can create an action item that everyhour on the hour will trigger and will change a value in the data which is written to the database. This way you will have a point that will show every hour on the hour. For this one once you have the trigger in your database then you can query for MIN and MAX and use the bits you created. It is rather involved but seems to work ok.

The other method is more or less a timed event

oldvalue = datepart(h,current_timestamp)
{then you would use the date arithmetic option here to set your begining and ending time}
if oldvalue != storedvalue:
     fpmi.db.runQuery("select {your criteria} from {your database} where {your time date field}< start and {your time date field}> end","{name of your instance}")
     storedvalue = oldvalue

Hope this helps and is clear enough, I am not the best as describing things

3

:scratch: I don’t think you need to do anything. Just store the minute data, and get the hourly summaries on demand with a grouping query. For SQL Server the syntax would be something like:

SELECT AVG(mycolumn) FROM mytable WHERE (date range clause) GROUP BY DATEPART(yyyy, t_stamp), DATEPART(dy, t_stamp), DATEPART(hh, t_stamp)

4

I second Carl’s answer. If for some reason you really did want to store it to a different table (for example, because you only want to store the last 5 days in minute format, but forever in avg format), look into using the INSERT SELECT form of the insert query.

The documentation for SQL Server’s INSERT statement can be found here. But basically, you can do an insert based on a select, which I would then make into a stored procedure and call on a timer from FactorySQL.

But to keep it simple, consider just querying it as-needed like Carl suggested.