Return value of a column based on the date column taking the value closest to xx:00 and xx:30

user_14331206 0
Viewed 48

I am trying to return data in 30minute intervals but if there are no data at 30mins i would like to take the value closest to the 30min or 0 min mark with a tolerance of -5 +5, if there are still no data with this range of +5 -5 which is 55-05 or 25-35 there will be no value returned. Currently i have a sql statement that can return the average values from 55-05 and 25-35 in intervals of 30mins but i only want 1 value that is closest to the 30min now, i would be fine with 55-00 or 25-30 if 55-05 and 25-35 cannot be done.The sql statement i have now is

SELECT  [target],AVG([Temperature]) as Temperature,AVG([Humidity]) As Humidity,DATEADD(minute,(datediff(minute,0,[Date Time])/30)*30,0) as [Date Time],COUNT (*)                                    
FROM mytable                                    
WHERE DATEPART(minute, [Date Time]) IN (0,1,2,3,4,5,25,26,27,28,29,30,31,32,33,34,35,55,56,57,58,59)                                    
AND (CAST([Date Time] AS DATE)= '2020-09-17')                                   
AND ([target] = '1')                                    
                                    
GROUP BY [target],DATEADD(minute,(datediff(minute,0,[Date Time])/30)*30,0) ORDER BY [Date Time] ASC
output is
Target  Temperature Humidity    Date Time              (No column name)
1       21.100000   76.900000   2020-09-17 00:00:00.000      2
1       21.200000   77.300000   2020-09-17 01:00:00.000      1
1       21.133333   77.333333   2020-09-17 01:30:00.000      3
1       21.100000   77.400000   2020-09-17 02:00:00.000      1
1       21.100000   77.550000   2020-09-17 02:30:00.000      2
1       21.120000   77.640000   2020-09-17 03:00:00.000      5

Expected output should be roughly the same with no column name being 1 since only 1 value should be returned, only changes would be temperature and humidity not being average of range of the time 25-35 and 55-05, so the temperature and humidity data taken should be the 1 closest to the 30min and 0min mark

sql-server-2016 sql-server sql
1 Answers

You can use ROW_NUMBER(). But the key idea is to round the time values to the appropriate "time group". Then you can use the difference between the timegroup time and the observed date time to order the rows:

SELECT  t.*                                
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY t.target, v.timegrp
                                ORDER BY ABS(DATEDIFF(SECOND, t.[Date Time], v.timegrp))
                               ) as seqnum
      FROM mytable t CROSS APPLY
           (VALUES (DATEADD(MINUTE,
                            (DATEDIFF(MINUTE, 0, DATEADD(MINUTE, 5, t.[Date Time])) / 30) * 30,
                            0)
                   )
           ) v(timegrp)
      WHERE CAST([Date Time] AS DATE) = '2020-09-17' AND                        
            [target] = '1' AND
            v.timegrp > DATEADD(MINUTE, -5, t.[Date Time]) AND
            v.timegrp < DATEADD(MINUTE, 5, t.[Date Time])
     ) t                                  
WHERE seqnum = 1;

Note: This logic does not work for midnight, because the time interval spans multiple days. To handle that, you can move the filtering to the outer query. I haven't done that because your query doesn't take this into account.

user_11440350
Related