Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: SQL to combine date ranges
 
From:   fred & uli

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