Converting a half-hourly data into Quarter-hourly data by taking mean of half-hourly values in SQL?-CodePudding

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?

CodePudding user response：

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