1

I have values inserted with random intervals in the table. I would like to fetch the first (if exists) value in (say) 5 minutes interval. So is required to have 5 minutes column and the values column should express the first possible value before the next 5 minute range. Using the aggregate function it is possible to get min, max, avg ... which is not the case here. I need the first possible value in the range.

Id  TSDest                  Value
1   2019-02-18 08:31:00.000 3472
2   2019-02-18 08:35:00.000 3471
3   2019-02-18 08:39:00.000 3464
4   2019-02-18 08:43:00.000 3462
5   2019-02-18 08:47:00.000 3456
6   2019-02-18 08:51:00.000 3455
7   2019-02-18 08:55:00.000 3463
8   2019-02-18 08:59:00.000 3466
9   2019-02-18 09:04:00.000 3166

I need the values aligned to a constant time interval e.g. 5 minutes (A plus feature would be to get the nearest timestamp value in the range).

Id  TSDest                  Value
1   2019-02-18 08:30:00.000 3472
2   2019-02-18 08:35:00.000 3471

4   2019-02-18 08:40:00.000 3462
5   2019-02-18 08:45:00.000 3456
6   2019-02-18 08:50:00.000 3455
7   2019-02-18 08:55:00.000 3463

9   2019-02-18 09:00:00.000 3166

Help appreciated,

2

Assuming seconds always are '00.000':

WITH cte AS ( SELECT Id, 
                     DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) TSDest, 
                     Value,
                     ROW_NUMBER() OVER (PARTITION BY DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) 
                                        ORDER BY TSDest ASC) rn
              FROM test )
SELECT Id, TSDest, Value
FROM cte
WHERE rn = 1
ORDER BY TSDest

fiddle


Added by OP

Thank you very much, this was very helpful. BTW, because I had records with seconds and milliseconds, I had to add cast the time to "smalldatetime" which did the trick very well,

WITH cte AS ( SELECT Id, 
                         cast(DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) as smalldatetime) TSDest, 
                         Value,
                         ROW_NUMBER() OVER (PARTITION BY cast(DATEADD(mi, -DATEPART(mi, TSDest) % 5, TSDest) as smalldatetime)  
                                            ORDER BY TSDest ASC) rn
                  FROM test )
    SELECT Id, TSDest, Value
    FROM cte
    WHERE rn = 1
    ORDER BY TSDest

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.