Archives of the TeradataForum

Message Posted: Fri, 16 Jun 2006 @ 16:48:56 GMT

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

Anomy

This isn't elegant by any stretch of the imagination but I think it works.

You should be able to tailor it to run on your system.

     CREATE SET TABLE mytable
          (
           col1 INTEGER,
           col2 INTEGER)
     PRIMARY INDEX ( col1 );

     insert into mytable(2,200);
     insert into mytable(6,10);
     insert into mytable(9,400);
     insert into mytable(13,60);

This generates your sequence numbers

     sel
     date-calendar_date
     from sys_calendar.calendar t1
     ,mytable t2
     where
     t1.calendar_date  between date-col1 and date
     qualify(rank(col1 desc))=1
     order by 1;

This generates your ranges

     sel
     case when t1.col1 is not null then t1.col1 else 0 end
     ,case when t2.col1 is not null then t2.col1 else t1.col1+1 end
     ,case when t1.col1 is not null then t1.col2 else t2.col2 end

     from mytable t1 full outer join
     mytable t2
     on
     t2.col1>t1.col1

     group by t1.col1
     qualify rank(t2.col1 asc)=1
     order by 1;

A combination of both gives you what you want (don't know what performance would be like on large tables but the logic appears to work)

     sel
     t3.seq_no
     ,t4.col2
     from
     (
     sel
     date-calendar_date
     from sys_calendar.calendar t1
     ,mytable t2
     where
     t1.calendar_date  between date-col1 and date
     qualify(rank(col1 desc))=1
     ) as t3 (seq_no)
     ,
     (
     sel
     case when t1.col1 is not null then t1.col1 else 0 end
     ,case when t2.col1 is not null then t2.col1 else t1.col1+1 end
     ,case when t1.col1 is not null then t1.col2 else t2.col2 end

     from mytable t1 full outer join
     mytable t2
     on
     t2.col1>t1.col1

     group by t1.col1
     qualify rank(t2.col1 asc)=1
     ) as t4(col1_start,col1_end,col2)
     where
     t3.seq_no between t4.col1_start and t4.col1_end-1

     order by 1,2

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