create table prices
(price_type integer,
 eff_date date,
 price number(10,2));

INSERT INTO prices (price_type,eff_date,price)
VALUES (100,'05-JAN-04',4.99);
INSERT INTO prices (price_type,eff_date,price)
VALUES (100,'06-JAN-04',4.99);
INSERT INTO prices (price_type,eff_date,price)
VALUES (100,'07-JAN-04',4.99);
INSERT INTO prices (price_type,eff_date,price)
VALUES (101,'08-JAN-04',3.49);
INSERT INTO prices (price_type,eff_date,price)
VALUES (101,'09-JAN-04',3.49);
INSERT INTO prices (price_type,eff_date,price)
VALUES (101,'10-JAN-04',3.49);
INSERT INTO prices (price_type,eff_date,price)
VALUES (100,'11-JAN-04',4.99);
INSERT INTO prices (price_type,eff_date,price)
VALUES (100,'12-JAN-04',4.99);
INSERT INTO prices (price_type,eff_date,price)
VALUES (101,'13-JAN-04',3.99);
INSERT INTO prices (price_type,eff_date,price)
VALUES (101,'14-JAN-04',3.99);
INSERT INTO prices (price_type,eff_date,price)
VALUES (100,'15-JAN-04',4.99);
INSERT INTO prices (price_type,eff_date,price)
VALUES (100,'16-JAN-04',4.99);


select 
  price_type, 
  min(eff_date) start_date, 
  max(eff_date) end_date, 
  price 
from prices
group by price_type, price;

select substr(path,1,case when instr(path,'/',1,3)=0 then length(path) else instr(path,'/',1,3)-1 end), min(eff_date), max(eff_date), min(price), count(*)
from
(
	select SYS_CONNECT_BY_PATH(recnum, '/') Path, recnum, price, eff_date
	    from 
	(
	    select 
		rownum recnum , price_type, eff_date, price,
		case when lag(price,1) over (order by eff_date) = price then lag(rownum,1) over (order by eff_date) else 0 end prev 
		from prices
		union 
	    select 0 ,null,null,null,null from dual    
	) x
	connect by prior recnum=prev
	start with recnum=0
)
group by substr(path,1,case when instr(path,'/',1,3)=0 then length(path) else instr(path,'/',1,3)-1 end)
having length(substr(path,1,case when instr(path,'/',1,3)=0 then length(path) else instr(path,'/',1,3)-1 end))>2

CREATE OR REPLACE 
PROCEDURE price_time_grouping
IS
   v_price_type            INTEGER;
   v_previous_price_type   INTEGER;
   v_eff_date              DATE;
   v_group_counter         INTEGER := 1;

   CURSOR cur
   IS
      SELECT   price_type, eff_date
          FROM prices
      ORDER BY eff_date ASC;
BEGIN
   OPEN cur;

   LOOP
      FETCH cur
       INTO v_price_type, v_eff_date;

      EXIT WHEN cur%NOTFOUND;

      IF v_price_type = NVL (v_previous_price_type, v_price_type)
      THEN
         NULL;
      ELSE
         v_group_counter := v_group_counter + 1;
      END IF;

      UPDATE prices
         SET time_group = v_group_counter
       WHERE eff_date = v_eff_date;

      COMMIT;
      v_previous_price_type := v_price_type;
   END LOOP;

   CLOSE cur;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

select price_type, min(eff_date), max(eff_date), price
  from (
    select eff_date, price_type, price, max(rn) over (order by eff_date) grp
      from (
        select eff_date, price_type, price,
               case when lag(price_type) over (order by eff_date) <> price_type
                    then row_number() over (order by eff_date)
                end rn
          from prices
           )
       )
 group by price_type, price, grp
 order by grp nulls first

with x as 
(
        select 
		rownum recnum , price_type, eff_date, price,
		case when lag(price,1) over (order by eff_date) = price then lag(rownum,1) over (order by eff_date) else 0 end prev 
		from prices
        order by eff_date
)
select min(price_type),min(eff_date), max(eff_date), price
from
( 
	select x.*, case when lag(price,1) over (order by eff_date) = price then lag(recnum,1) over (order by eff_date) else recnum end grouping
	from x  
	where x.prev=0 or x.recnum in (select recnum-1 from x where x.prev=0) 
)
group by grouping, price