|
Archives of the TeradataForumMessage Posted: Fri, 16 Jun 2006 @ 16:48:56 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||