Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


Subj:   Re: SQL to combine date ranges
 
From:   Ulrich Arndt

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020