I have half-hourly (30 min interval) Data Table in SQL (SSMS). I want to convert it to Quarter-hourly (15 min interval) by taking mean/average of the preceding and succeeding values. What SQL query should I run for it?
Using cross apply to convert a single row to a pair of rows
select t2.* from ( select *, dateadd(minute, 15, timestamp) ts2, (value + lead(value) over(order by timestamp))/2.0 v2 from tbl ) t cross apply ( values (timestamp, value), (ts2, v2) ) t2 (timestamp, value) where t2.value is not null order by t2.timestamp