The problem is that I want to get every n'th record from table but based on datetime.

I have table where I add record every 30 minutes with current state for each of my Sub objects, something like below:

And now I need to get points for one Sub object in certain period. But I dont want to get all entires cause I can end with too many points, I just want to get sometimes 1 per hour or 1 per day (it may change)

I already tried with ROW_NUMBER as I know that I'm adding point every 30 minutes but cause I need add where clausure for SubId then I might end with incorrect result (cause I'm adding or removing those Subobject in meanwhile)

    SELECT * FROM (
    SELECT [Id]
      ,[SubId]
      ,[Color]
      ,[Timestamp]
      , ROW_NUMBER() OVER (ORDER BY OccupancyHistoryId) as rownum
    FROM [dbo].[Table]) AS t
 WHERE t.SubId = '7EB43D1D-7274-41C4-35DA-08D727A424E6' AND t.rownum % 2 = 0

Am I miss something obviouse? Or maybe my approach is wrong?

Expected result: For e.g records from 2022-06-27 to 2022-06-28 but only 1 per each 2 hours.

CodePudding user response：

Thanks to @ourmandave's comments, I was able to resolve the problem. I didn't notice that I can use DATEDIFF with %.

So, to get entries only one per two hours, I simply write the query like that. So, obviously:

SELECT *
  FROM [dbo].[Table] WHERE DateDiff(Minute, 0, TimestampUtc) % 120 = 0