# Intervals query of huge data

My question is more or less theoretical - what is the best approch to get query data by intervals.

The situation

I have a system that manage counters (thousands of counters - counts consumption of electricity/water etc...), every 15 minutes the counter is sampled and stored in the DB. there is one table that stores all counters data [id, counterID, Timestamp, CounterValue].

Now, I want to query the data about the Interval (hourly/daily/weekly/monthley) consumption.

The intervaled consumption defined as the first measurment (counting) in the start of the next interval minus the first measurment in the start of the current interval. since all measurments are agregated (counters) the delta between two counters represents the consumption in the interval.

Since there is ALOT of data and I want the queries to be fast I need a better way then just quering the whole table... (I think...) quering the whole data (even with small amount of data - this query is slow...):

``````SELECT *, dateadd(hour,datediff(hour,0, dateOfData),0) as roundedTime
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY  counterId,datediff(hour, 0, dateOfData) ORDER BY dateOfData asc) row_number
FROM counters_data) A
WHERE row_number = 1
Order by dateOfData;
``````

I thought about some ways doing this:

1. Add [isHourly,isDaily,isWeekly...] boolean columns in the measurments table and when I store the data I'll update those flags.
2. Add tables for every interval with columns [id, date, counterId, counterDataRecord] so there will be tblDailyCounter, tblWeeklyCounter etc...

is there any other idea that Im missing?

what is the pros&cons of those aproches?

what is the most recomanded / best practice?

• If you would have just a daily table that would contain the consumption (not the counters) for the day, wouldn't that be enough? Or at least I wouldn't create that many different tables since you can just sum up the figures -- although an indexed view would be simple to add. – James Z Jun 21 '15 at 9:30
• @JamesZ another issue is that sometimes I need to query the sum of many conters (all building consumbtion for instance) and that's why I think many tables are needed. but mainly Im looking for any other solution – yossico Jun 21 '15 at 10:34