Every three seconds... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Every three seconds...

Author

Topic

SQLJames
Starting Member

35 Posts

Posted - 2012-07-30 : 15:25:59

I pre-apologize for not adhering to the requirements for a post. I simply don't have the data to show. This was a request from another group and I am trying to give them examples without seeing the data. Insane yes but that is why it is called a job.

Here is the issue: we have a sensor that logs temp data every three seconds into a table. Timestamp is used for the entry. I want know what the temperature is from the table for a date range such as July 1 to July 31 and ONLY for once per hour. It doesn’t matter what part of the hour. I will start by assuming that it is on the hour for example 7/1/2012 03:00:00 . Does anyone have any suggestions for where to start on this?

Again, I apologize for not having the preferred posting method but this is what I was given. I can’t even see the data. I appreciate any help in advance and thank you for your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 15:42:17

you've to create an automated job in sql server agent which gets executed as per hourly schedule. it should call a sql procedure which will calculate temperature for required date range

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-30 : 15:43:17

SELECT DATEADD(hour, DATEDIFF(hour, 0, dateCol), 0) hour,
MAX(Temp)
FROM myTable
WHERE dateCol BETWEEN '2012-07-01' AND '2012-07-31'
GROUP BY DATEDIFF(hour, 0, dateCol)
ORDER BY hour

SQLJames
Starting Member

35 Posts

Posted - 2012-07-30 : 15:45:49

Thanks Rob! That is just perfect!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-30 : 15:47:16

One tweak:

SELECT DATEADD(hour, DATEDIFF(hour, 0, dateCol), 0) hour,
MAX(Temp)
FROM myTable
WHERE dateCol BETWEEN '2012-07-01' AND '2012-07-31 23:59:59.997'
GROUP BY DATEDIFF(hour, 0, dateCol)
ORDER BY hour

SQLJames
Starting Member

35 Posts

Posted - 2012-07-30 : 15:51:34

Thank you again for your help and speed. I can't express how much I appreciate the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources