Sum consecutive values and assign highest row

CREATE TABLE TABLE_ONE ( 
   person varchar(5), 
   COL_1 varchar(20), 
   COL_2 varchar(20), 
   value int, 
   row_num int
);
 
 
INSERT INTO table_one VALUES 



('101','A','ABC',10,1)
('101','A','ABC',20,2)
('101','A','ABC',12,6)
('101','A','ABC',10,8)
('101','A','ABC',20,9)
('101','A','ABC',15,10)
('101','A','ABC',10,12)
('101','B','ABC',1,1)
('101','B','ABC',4,2)
('101','B','ABC',2,3)
('101','B','ABC',1,10)
('101','B','ABC',3,11)
('101','B','ABC',4,15)
('101','B','ABC',4,16)

Need to add column 'value' based on consective row_num. If the values are consective in row_num then we add the values and assign the highest row_num value. For example, row_num = 1 and row_num = 2 are consecutive, so we add the values and we get 30 as the value.

Output Screenshot

CodePudding user response：

If I understand the expected results correctly, the following statement is a possible solution. You need to define the appropriate groups, then sum the value column and get the max row_num for each group.

SELECT person, col_1, col_2, SUM([value]) AS [value], MAX(row_num) AS row_num
FROM (
   SELECT 
      *, 
      row_num - ROW_NUMBER() OVER (PARTITION BY person, col_1, col_2 ORDER BY row_num) AS [group]
   FROM TABLE_ONE
) t
GROUP BY person, col_1, col_2, [group]  
ORDER BY person, col_1, col_2, [group]

CodePudding user response：

Using