CODEWITHSUNDEEP
sqlsql-serverdatabasedatetimesql-server-2008-r2
CeCe
CeCe

Reputation: 21

SQL select one row for every n minutes

I'm using Microsoft SQL Server 2008, and have a data set that has entries for every few minutes, over a long period of time. I am using a program to graph the data, so i need to return about 20 values per hour. Some days the data is every minute, sometimes every five minutes, and sometimes every 8 or 9 minutes, so selecting every nth row won't give an even spread over time

eg for a sample in 2012, it looks like this :

DateTime
2012-01-01 08:00:10.000
2012-01-01 08:08:35.000
2012-01-01 08:17:01.000
2012-01-01 08:25:26.000

and for a sample the next year it looks like this:

DateTime
2013-07-20 08:00:00.000
2013-07-20 08:01:00.000
2013-07-20 08:02:00.000
2013-07-20 08:03:00.000
2013-07-20 08:04:00.000

at the moment I am using a statement like this:

SELECT * FROM [Master]
WHERE (((CAST(DATEPART(hour, DateTime)as varchar(2)))*60)
      +CAST(DATEPART(minute, DateTime)as varchar(2))) % '5' = '0' 

ORDER BY DateTime

This works fine for july 2013, but I miss most points in 2012, as it returns this

DateTime
2012-01-01 08:00:10.000
2012-01-01 08:25:26.000
2012-01-01 08:50:43.000
2012-01-01 09:15:59.000
2012-01-01 10:40:14.000
2012-01-01 11:05:30.000

What better way is there to do this?

EDIT: The table has a DateTime column, and a pressure column, and I need to output both and graph pressure against date and time.

Upvotes: 2

Views: 4891

Answers (1)

Siyual
Siyual

Reputation: 16917

Since they can be random for the hours, this should work for what you need:

Declare @NumberPerHour Int = 20

;With Cte As
(
    Select  DateTime, Row_Number() Over (Partition By DateDiff(Hour, 0, DateTime) Order By NewId()) RN
    From    Master
)
Select  DateTime
From    Cte
Where   RN <= @NumberPerHour
Order By DateTime Asc

This will group the rows by the hour, and assign a random Row_Number ID to them, and only pull those with a Row_Number less than the number you're looking for per hour.

Upvotes: 4

Related Questions