Question:
How can I calculate with only the first values for every duration (small interval) in a interval?
Details:
I have (many) counters (counterId column). each counter is sampled from time to time and the sample is stored in "counter_samples" table:
counter_samples table:
id |counterId | value | Time |
1 | 1 | 100 | 2015-06-12 14:00:04 |
2 | 2 | 57 | 2015-06-12 14:00:11 |
3 | 1 | 100 | 2015-06-12 15:00:43 |
4 | 2 | 59 | 2015-06-12 15:00:24 |
5 | 1 | 104 | 2015-06-12 16:00:12 |
6 | 2 | 63 | 2015-06-12 16:00:14 |
7 | 1 | 110 | 2015-06-12 17:00:25 |
8 | 2 | 70 | 2015-06-12 17:00:28 |
9 | 1 | 120 | 2015-06-12 18:00:17 |
10 | 2 | 80 | 2015-06-12 18:00:36 |
11 | 1 | 130 | 2015-06-12 19:00:04 |
12 | 2 | 90 | 2015-06-12 19:00:13 |
13 | 1 | 170 | 2015-06-12 20:00:52 |
14 | 2 | 95 | 2015-06-12 20:00:13 |
15 | 1 | 190 | 2015-06-12 21:00:11 |
16 | 2 | 99 | 2015-06-12 21:00:04 |
As you can see, as the time pass every counter value always bigger or equal to the value before (equal=no counting in the timespan)
What I want is for any given interval (@start_date AND @end_date) to query the samples table about duration (hour/day/week/month etc..) and get the total counting for every duration in the interval.
In example, if the interval is: 1/1/2014 - 1/1/2015 and the duration is month the result set will be 12 rows that represent the 12 month in the interval and month value for month 'i' will be month[i+1].value - month[i].value.
To calculate the counting I want to substract the first record of the next duration from the first record of the current duration - for the example above, if the duration is 2 hours what i want to get is (for counterId=1):
|CounterId | count | Time |
| 1 | 4 | 2015-06-12 14:00:00 |
| 1 | 16 | 2015-06-12 16:00:00 |
| 1 | 50 | 2015-06-12 18:00:00 |
And for counterId=2:
|CounterId | count | Time |
| 2 | 6 | 2015-06-12 14:00:00 |
| 2 | 17 | 2015-06-12 16:00:00 |
| 2 | 15 | 2015-06-12 18:00:00 |
(notice in the two queries above the time 20:00:00 is ignored becaus the is no record for 22:00:00 and above)
The time must be changed to match the (intervalStart + duration * k) format because I want to be able to groupBy and sum counters By time in a different query so for the above example to get:
| count | Time |
| 10 | 2015-06-12 14:00:00 |
| 33 | 2015-06-12 16:00:00 |
| 65 | 2015-06-12 18:00:00 |
I've been searching here & there for a good example for this issue but didnt solved it with what I found: Sql to select row from each day of a month
Select first row in each GROUP BY group?
When should I use Cross Apply over Inner Join?
Thanks!
This question was long time ago. anyways, I solved it in sort of ugly way. I created a separate table for every relevant duration (day/weed/month etc) and the duration table holds FK to to the first report of every interval raw data - this way I can query for knowen intervals and pull the raw values for it.