Thanks for providing sample data and output. Please post the data in create table + insert form however - it makes it much easier to understand what's going on.
A brief description of the problem also helps! I'm guessing that you want to assign a new number every time the res_type changes, ordered by DS (= date start?)
Here's one way you can do this:
- First find whenver the res_type changes by comparing it to the value in the previous row (using lag). Assign value when this happens (e.g. with rownum)
- "Fill down" these assigned values for each res_type group
- Use dense_rank() to assign the flag rank you want
create table t (
ds date, de date, res_type integer
);
insert into t values ('12.10.2015 14:00:00','12.10.2015 14:20:00',3);
insert into t values ('12.10.2015 14:20:00','12.10.2015 14:40:00',3);
insert into t values ('12.10.2015 14:40:00','12.10.2015 15:00:00',3);
insert into t values ('12.10.2015 15:00:00','12.10.2015 15:20:00',0);
insert into t values ('12.10.2015 15:20:00','12.10.2015 15:40:00',0);
insert into t values ('12.10.2015 15:40:00','12.10.2015 16:00:00',0);
insert into t values ('12.10.2015 16:00:00','12.10.2015 16:20:00',1);
insert into t values ('12.10.2015 16:20:00','12.10.2015 16:40:00',1);
insert into t values ('12.10.2015 16:40:00','12.10.2015 17:00:00',0);
insert into t values ('12.10.2015 17:00:00','12.10.2015 17:20:00',0);
insert into t values ('12.10.2015 17:20:00','12.10.2015 17:40:00',0);
insert into t values ('12.10.2015 17:40:00','12.10.2015 18:00:00',0);
insert into t values ('12.10.2015 18:00:00','12.10.2015 18:20:00',0);
insert into t values ('12.10.2015 18:20:00','12.10.2015 18:40:00',0);
insert into t values ('12.10.2015 18:40:00','12.10.2015 19:00:00',0);
insert into t values ('12.10.2015 19:00:00','12.10.2015 19:20:00',3);
commit;
select ds, de, res_type,
--assign the final flag
dense_rank() over (order by mx) flag
from (
select t.*,
-- fill down the chagne flags
max(r) over (order by ds) mx
from (
select t.*,
-- find the changes in res_type
case
when nvl(lag(res_type) over (order by ds),-1) != res_type then rownum
end r
from t
) t
);
DS DE RES_TYPE FLAG
------------------- ------------------- ---------- ----------
12/10/2015 14:00:00 12/10/2015 14:20:00 3 1
12/10/2015 14:20:00 12/10/2015 14:40:00 3 1
12/10/2015 14:40:00 12/10/2015 15:00:00 3 1
12/10/2015 15:00:00 12/10/2015 15:20:00 0 2
12/10/2015 15:20:00 12/10/2015 15:40:00 0 2
12/10/2015 15:40:00 12/10/2015 16:00:00 0 2
12/10/2015 16:00:00 12/10/2015 16:20:00 1 3
12/10/2015 16:20:00 12/10/2015 16:40:00 1 3
12/10/2015 16:40:00 12/10/2015 17:00:00 0 4
12/10/2015 17:00:00 12/10/2015 17:20:00 0 4
12/10/2015 17:20:00 12/10/2015 17:40:00 0 4
12/10/2015 17:40:00 12/10/2015 18:00:00 0 4
12/10/2015 18:00:00 12/10/2015 18:20:00 0 4
12/10/2015 18:20:00 12/10/2015 18:40:00 0 4
12/10/2015 18:40:00 12/10/2015 19:00:00 0 4
12/10/2015 19:00:00 12/10/2015 19:20:00 3 5