Archives of the TeradataForum

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

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

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:

     row_number()
     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

or

     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...

Dieter

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