CODEWITHSUNDEEP
sqlsql-servervb.net
Lalit
Lalit

Reputation: 13

Select Every Nth Record From SQL

I am having a Database in which data is been logged in regular interval of time i e for 5 minutes say it is been logged for 24 hours as shown in below table.

       Date and Time        Value
    2016-09-17 14:00:00      25.26
    2016-09-17 14:05:00      24.29
    2016-09-17 14:10:00      25.22
    2016-09-17 14:20:00      25.10





    2016-09-17 23:55:00      20.21

I want To display Every 1 hour reading using SQL query There are chances the some reading may be missing The expected Output should be.

     Date and Time        Value
    2016-09-17 14:00:00      25.26
    2016-09-17 15:00:00      27.29
    2016-09-17 16:00:00      28.12
    2016-09-17 17:00:00      22.11

There are chances my be that some reading may be missing. like

       Date and Time        Value
    2016-09-17 14:35:00      25.26

This reading may be missing Please Suggest SQL query for the same

Upvotes: 0

Views: 387

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

SELECT t1.DateCol,
       t1.Value
FROM yourTable t1
INNER JOIN
(
    SELECT MIN(DateCol) AS firstDate
    FROM yourTable
    GROUP BY FORMAT(DateCol, 'dd/MM/yyyy hh')
) t2
    ON t1.DateCol = t2.firstDate

If you instead wanted to group by every 15 minutes, you could try:

GROUP BY CONCAT(FORMAT(DateCol, 'dd/MM/yyyy hh'),
                FLOOR(DATEPART(MINUTE, DateCol) / 15))

Upvotes: 2

Related Questions