-
Viorel 94,021 Reputation points
2021-03-06T18:47:02.173+00:00 Check one of possible solutions:
; with E1 as ( select *, lag([Value]) over (order by [Date]) as [prev], lead([Value]) over (order by [Date]) as [next] from MyTable ), E2 as ( select *, (select max([Date]) from E1 where [prev] is null and [Value] is not null and [Date] <= e.[Date]) sd from E1 e ) select [Date], (select sum([Value]) from MyTable where [Date] between sd and e.[Date]) [Value] from E2 e where [Value] is not null and [next] is null order by [Date]
Thanks Viorel!
Not used the LAG and LEAD functions before - I will investigate!
Cheers,
Matty