sql - Reduce/Summarize and Replace Timestamped Records -

i have sql table has timestamped records server performance data. data polled , stored every 1 minute multiple servers. want keep data large period of time reduce number records data older 6 months.

for example, have old records so:

    timestamp  server  cpu  app1  app2 1   ... 00:01  host1   5    1     10     2   ... 00:01  host2   10   5     20 3   ... 00:02  host1   6    0     11 4   ... 00:02  host2   11   5     20 5   ... 00:03  host1   4    1     9 6   ... 00:04  host2   9    6     19 

i want able reduce data every minute every 10 minutes or possibly every hour older data.

my initial assumption i'd average values times within 10 minute time period , create new timestamped record after deleting old records. create sql query generates insert statements new summarized records? query like?

or there better way accomplish summarization job?

you might want consider moving summarized information different table don't end in situation you're wondering if you're looking @ "raw" or summarized data. other benefits include max, min, stddev , other values along avg.

the tricky part chunking out times. best way think of start output convert(blah, timestamp, 120) function:

-- result: 2015-07-08 20:50:55 select convert(varchar(19), current_timestamp, 120) 

by cutting off after hour or after 10-minute point can truncate times:

-- hour; result 2015-07-08 20 select convert(varchar(13), current_timestamp, 120)  -- 10-minute point; result 2015-07-08 20:50:5 select convert(varchar(15), current_timestamp, 120) 

with little more massaging can fill out minutes either 1 , cast datetime or datetime2:

-- hour increment cast(convert(varchar(13), current_timestamp, 120) + ':00' datetime)  -- 10-minute increment cast(convert(varchar(15), current_timestamp, 120) + 0' datetime) 

using logic above, times truncated. in other words, hour formula convert timestamp 11:00 <= timestamp < 12:00 11:00. minute formula convert timestamp 11:20 <= timestamp < 11:30 11:20.

so better part query looks (i've left out getting rid of rows you've summarized):

-- hour-increment version insert mytableorothertable select   cast(convert(varchar(13), [timestamp], 120) + ':00' datetime),   avg(cpu),   avg(app1),   avg(app2) mytable group   cast(convert(varchar(13), [timestamp], 120) + ':00' datetime)