add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
CODE1 VARCHAR(1) NOT NULL
,CODE2 VARCHAR(1) NOT NULL
,CODE3 VARCHAR(1) NOT NULL
,RATE INTEGER NOT NULL
,VALUE INTEGER NOT NULL
,MONTH INTEGER NOT NULL
);


begin
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',1,1,202001);
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',1,1,202002);
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',1,1,202003);
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',2,1,202004);
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',2,1,202005);
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',1,1,202006);
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',1,1,202007);
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',1,1,202008);
INSERT INTO mytable(CODE1,CODE2,CODE3,RATE,VALUE,MONTH) VALUES ('A','B','C',1,1,202009);
end;
/
1 rows affected
select code1, code2, code3, rate, value, min(month) start_dt,
case when row_number() over(partition by code1, code2, code3 order by max(month) desc) = 1 then 999912 else max(month) end end_dt
from (
select t.*,
row_number() over(partition by code1, code2, code3 order by month) rn1,
row_number() over(partition by code1, code2, code3, rate, value order by month) rn2
from mytable t
) t
group by code1, code2, code3, rate, value, rn1 - rn2
order by start_dt
CODE1 CODE2 CODE3 RATE VALUE START_DT END_DT
A B C 1 1 202001 202003
A B C 2 1 202004 202005
A B C 1 1 202006 999912