Can't use SQL lag sum

Question

My query is like this

SELECT
    [day], [time],AvaliableTimes,
    CASE
       WHEN AvaliableTimes > 0 
          THEN SUM(AvaliableTimes) OVER (ORDER BY [day], [time], AvaliableTimes)
          ELSE 0 
    END AS SumValue
FROM
    [AvailableTimes] 
WHERE
    [day] = 1 AND BranchAreaId = 1
ORDER BY
    [day], [time], AvaliableTimes

I want to start sum from 0 if value is null or 0.

Results:

Answer 1

you can use a recursive CTE to do it. Perform the cumulative sum in the rcte and if AvailableTimes = 0, reset it

; with
cte as
(
    select  *, rn = row_number() over (order by time)
    from    yourtable   
),
rcte as
(
    select  *, sumvalues = AvailableTimes
    from    cte
    where   rn = 1

    union all

    select  c.*, sumvalues = case when  c.AvailableTimes <> 0
                      then  r.sumvalues + c.AvailableTimes
                      else  c.AvailableTimes
                      end
    from    cte c
        inner join rcte r   on  c.rn    = r.rn + 1
)
select  day, time, AvailableTimes, sumvalues
from    rcte
order by time