Archives of the TeradataForum

Message Posted: Mon, 24 Jun 2002 @ 15:34:48 GMT

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

Hi,

This works but I am not sure if it also works with high volumes. At least replace the two derived tables with a volatile table and populate the temp table first before you are use it in the select.

Kind regards

Ulrich

EXAMPLE SQLs:

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);

select id,
         price,
         min(calendar_date) as valid_from,
         max(calendar_date) as valid_until
from
        (
        select r1.id,
                 r1.price,
                 r1.calendar_date,
                 case when r2.id is null then 1 else 0 end as last_ind,
                 csum(last_ind,r1.calendar_date desc) interval_flag
        from
                /* two times the same derived table - better with a
volatile table */
                (
                select id,
                     price,
                     calendar_date,
                     rank(calendar_date) as r
                from  a,
                    sys_calendar.calendar c
                    where c.calendar_date between a.valid_from and a.valid_until
                group by 1,2
                ) r1
                left outer join
                (
                select id,
                     price,
                     calendar_date,
                     rank(calendar_date ASC) as r
                from  a,
                    sys_calendar.calendar c
                    where c.calendar_date between a.valid_from and a.valid_until
                group by 1,2
                ) r2
                        on r1.id = r2.id
                           and r1.price = r2.price
                           and r1.calendar_date + 1 = r2.calendar_date
        ) as t
group by id,price,interval_flag
order by 1,3,2
;

drop table a;

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