I want to run a query that will display an average temperature value for every hour of the previous day displayed from midnight onwards.
The Table has column UpdateOn with timestamp in format 2018-01-10 13:56:06.000 and column P1Temp which has temperature data. The data is entered in the table approx every minute.
I have the following SQL query that displays data but only for the previous 24 hours from when the command is run. This can splits the data over two calendar days depending when the command is run, when I want it just to return every hour of just the previous day regardless of when the command is run.
select UpdateOn=dateadd(hh,datepart(hh,UpdateOn), cast(CAST(UpdateOn as date) as datetime)), round(AVG(P1Temp), 1)
where UpdateOn between dateadd(day,-1,getdate()) and getdate()
group by dateadd(hh,datepart(hh,UpdateOn), cast(CAST(UpdateOn as date) as datetime))
Result (splits over two calendar days but returns 25 results (24 hours))