0

Hi I have several tables in sql server that record data every 30 seconds. obviously after a while that gets a bit bulky, what I'd like to do is select 1 datapoint an hour over the past 24 hours and put that into a seperate tables for fast quesries, so I was thinking once every hour over a 24 hour period, 2 times a day over a week long period, and once a day over a month long period. I have datetime recorded on every data point we have.

I'd like something like this for the once an hour over 24 hours

Select * from MiscTBL Where Date >= (( Currentdatetime - 24 hh )) group by hh

thank you for any advice

Also I'm using sql server management studio it would be great if this were an automatically updating process so I had seperate tables I could use for faster queries of data over shorter pre quantified time periods

Cfoote7
  • 375
  • 2
  • 3
  • 14

1 Answers1

1

Something like this would return 1 sample per hour:

select *
from ActivityLog
where id in
    (select max(id) maxID
     from ActivityLog
     where activityDateTime between @startDateTime and @endDateTime
     group by DATEPART(hour, activityDateTime))

I would use this concept to build a stored proc that moved the data around and then I would schedule it to run as often as needed using a SQL Agent job.

JBrooks
  • 9,901
  • 2
  • 28
  • 32
  • I've been messing around with something simular but i dont want it to be a manual query, i'd like to have an automated import of that data into another table on a timed interval – Cfoote7 May 03 '14 at 19:23
  • Right, that is why I'm saying write a stored proc, then schedule it to run with every hour with SQL Agent. – JBrooks May 03 '14 at 19:43
  • I didn't know you could do that, awesome – Cfoote7 May 03 '14 at 20:27