Window Functions between current row and previous row with specific value

Question

I want to be able to sum the values in a certain column between the current row the latest previous row with a certain value in another column.

In this example I want to sum the Val Column between the current row and the latest row with a RecType of 2 partitioned by ID ordered by RowNum.

DECLARE @ExampleTable TABLE
(
    Id INT,
    RowNum INT,
    RecType INT,
    Val INT
)


INSERT INTO @ExampleTable
    (Id, RowNum, RecType, Val)
VALUES
    (1, 1, 1, 1),
    (1, 2, 2, 2),
    (1, 3, 1, 4),
    (1, 4, 1, 8),
    (1, 5, 1, 16),
    (1, 6, 2, 32),
    (1, 7, 1, 64),

    (2, 1, 2, 1),
    (2, 2, 2, 2),
    (2, 3, 1, 4),
    (2, 4, 1, 8),
    (2, 5, 1, 16),
    (2, 6, 1, 32),
    (2, 7, 2, 64)

I'm hoping for Results like:

DECLARE @Results TABLE
(
    Id  INT,
    RowNum INT,
    SumSinceLast2 INT
)


INSERT INTO @Results
    (Id, RowNum, SumSinceLast2)
VALUES
    (1, 1, 0),
    (1, 2, 0),
    (1, 3, 6), -- 4 + 2
    (1, 4, 14), -- 4 + 2 + 8
    (1, 5, 30), -- 16 + 8 + 4 + 2
    (1, 6, 62), -- 32 + 16 + 8 + 4 + 2
    (1, 7, 96), -- 64 + 32
    (2, 1, 0),
    (2, 2, 3), -- 2 + 1
    (2, 3, 6), -- 4 + 2
    (2, 4, 14), -- 8 + 4 + 2
    (2, 5, 30), -- 16 + 8 + 4 + 2
    (2, 6, 62), -- 32 + 16 + 8 + 4 + 2
    (2, 7, 126) -- 64 + 32 + 16 + 8 + 4 + 2

Is this something that I should be able to easily do in SQL Server 2017? I was hoping window functions would be of use here.

Answer 1

This doesn't return exactly what you want, but the results seem more sensible. Each "2" starts a new group. The values are then cumulatively summed within the group:

select e.*,
        (case when grp_2 = 0
              then 0
              else sum(val) over (partition by id, grp_2 order by rownum)
         end) as result
 from (select e.*,
              sum(case when RecType = 2 then 1 else 0 end) over
                  (partition by id
                   order by rownum
                  ) as grp_2
       from @ExampleTable e
      ) e
 order by id, rownum;

Here is a db<>fiddle.

The results can be tweaked (it makes for a much messier query) to "fix" the values for the "2" the way you have them. However, this version makes more sense to me, because "2" are not counted in two separate groups.

Here is a tweaked version that double counts the "2"s:

 select e.*,
        (case when grp_2 = 0 or grp_2 = 1 and RecType = 2
              then 0
              when RecType <> 2
              then sum(val) over (partition by id, grp_2 order by rownum)
              else sum(val) over (partition by id, grp_2_desc) + lag(val) over (partition by id, Rectype order by rownum)
         end) as result
 from (select e.*,
              sum(case when RecType = 2 then 1 else 0 end) over
                  (partition by id
                   order by rownum
                  ) as grp_2,
              sum(case when RecType = 2 then 1 else 0 end) over
                  (partition by id
                   order by rownum desc
                  ) as grp_2_desc
       from @ExampleTable e
      ) e
 order by id, rownum;