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 |