Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 25 May 2011 @ 10:12:44 GMT

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

Subj:   Re: OLAP function and tracking data changes
From:   Dieter Noeth

Hi Alan,

seems like you want to merge overlapping rows. So you need to calculate a dynamic partition number, a kind of RANK based on additional columns.

In TD13.10 you could the new RESET WHEN syntax, but for your example data this will work, too:

     over (order by start_date)
     - row_number()
        over (partition by field1, field2, field3
              order by start_date) as dummy

It's two STATS function steps in explan, but AFAIK there's no solution which results in a single step.

Based on this dummy column you do a

     select field1,field2,field3,min(start_date), max(end_date)
     from (...)
     group by field1,field2,field3,dummy


     select min(field1),min(field2),min(field3),min(start_date), max(end_date)
     from (...)
     group by dummy

or a similar OLAP function.

Of course you should check if your start/end dates are always adjacent, if not you probably don't want them to be combined in a single row. Then you to use a different approach...


  <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: 15 Jun 2023