Archives of the TeradataForum

Message Posted: Mon, 24 Jun 2002 @ 19:32:44 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi II,

you don't need the rank.

The descript solution works with all kind of history data - even if you have overlapping time periods for one id. In case you are sure that you have distinct time periods in your table the following will work also and should also work with real volumes:

create table a
(
    id integer,
    valid_from date format'yyyymmdd',
    valid_until date format'yyyymmdd',
    price decimal(15,2)
) primary index (id)
;

insert into a values (1,date-10,date-10,1.22);
insert into a values (1,date-9,date-8,0.99);
insert into a values (1,date-7,date-7,0.99);
insert into a values (1,date-6,date-4,0.99);
insert into a values (1,date-3,date-1,0.23);
insert into a values (1,date,date,0.23);
insert into a values (1,date+1,date+2,0.99);
insert into a values (1,date+3,date+4,0.99);

insert into a values (2,date-7,date-7,0.99);
insert into a values (2,date-6,date-4,0.99);
insert into a values (2,date-3,date-1,0.23);
insert into a values (2,date,date,0.23);
insert into a values (2,date+1,date+2,0.99);
insert into a values (2,date+3,date+4,0.99);

select id,
       price,
       min(valid_from) as valid_from,
       max(valid_until) as valid_until
from
     (
       select a1.id,
              a1.price,
              a1.valid_from,
              a1.valid_until,
              case when a2.id is null then 1 else 0 end as last_ind,
              csum(last_ind,a1.valid_from desc) interval_flag
       from a a1
              left outer join
              a a2
              on a1.id = a2.id
                 and a1.price = a2.price
                 and a1.valid_until + 1 = a2.valid_from
       group by a1.id
      ) as t
group by id,
         price,
         interval_flag
order by 1,3,2
;

drop table a;

	<Prev	Next>		<<First	<Prev	Next>	Last>>