In a MS SQL Sever DB, I have two fields. A value field, and a timestamp field. I want to get the value for the last timestamp for 2 particular hour(s) for each day. For example, with records like this:

I want only the data captured for the latest minute for each 7am and 7pm snapshot:

What I've tried for my code:

select * from tbl
where DATEPART(hour,[insert_datetime]) in ('07','19')
and DATEPART(minute,[insert_datetime]) in (select max(DATEPART(minute,[insert_datetime])) 
                                           from tbl 
                                           group by [insert_datetime]
                                           ) 

But there seem to have some problem with the way I "group" them, with the error message showing "Incorrect syntax near the keyword 'group'" Can any one help me out with this? TIA!

CodePudding user response：

In order to find the latest dates for your two hours, you can do following:

SELECT MAX(insert_datetime) AS insert_datetime FROM tbl
WHERE DATEPART(HOUR,insert_datetime) IN (7,19)
GROUP BY DATEPART(HOUR,insert_datetime)

If you also want to get the value for these dates, you can use the query as subquery:

SELECT value, insert_datetime FROM tbl WHERE insert_datetime IN
(SELECT MAX(insert_datetime) AS insert_datetime FROM tbl
WHERE DATEPART(HOUR,insert_datetime) IN (7,19)
GROUP BY DATEPART(HOUR,insert_datetime));

If you want to get this information for multiple days, you can extend the sub query and also group by the day:

SELECT value, insert_datetime FROM tbl WHERE insert_datetime IN
(SELECT MAX(insert_datetime) AS insert_datetime FROM tbl
WHERE DATEPART(HOUR,insert_datetime) IN (7,19)
GROUP BY DATEPART(HOUR,insert_datetime), 
CAST(insert_datetime AS DATE));

If you furthermore need to do a restriction on a certain date range, you can extend the WHERE clause. To check the date according to the example of your comment (only taking the last 10 days), this query will work:

SELECT value, insert_datetime FROM tbl WHERE insert_datetime IN
(SELECT MAX(insert_datetime) AS insert_datetime FROM tbl
WHERE DATEPART(HOUR,insert_datetime) IN (7,19)
AND DATEDIFF(DAY,insert_datetime,GETDATE()) BETWEEN 0 and 10
GROUP BY DATEPART(HOUR,insert_datetime), 
CAST(insert_datetime AS DATE));

CodePudding user response：

Since you're trying to find elements across two windows of time you might have better luck using row_number() with partitions, e.g.:

select
  [Value],
  insert_datetime
from (
  select
    [Value],
    insert_datetime,
    row_number() over (partition by datepart(hour, insert_datetime) order by insert_datetime desc) as [rn]
  from dbo.tbl
  where datepart(hour, insert_datetime) in (7, 19)
) Src
where rn=1;

Which yields the output: