Archives of the TeradataForum

Message Posted: Wed, 14 Jun 2006 @ 20:41:40 GMT

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

You can use Teradata ordered OLAP functions to created low and high banding numbers based on the first and last number in a windowing partition and then utilize the system calendar or other similar object to join between the bands, normalize, and create sequence numbers:

     select day_of_calendar - 1 as col1 ,
         col2
     from
         (select col1 ,
             case
                 when sum(col1) over (order by col1 rows between 1 preceding
     and 1 preceding) is null
                 then 0
                 else col1
             end as low_band,
             case
                 when sum(col1) over (order by col1 desc rows between 1
     preceding and 1 preceding) is null
                 then col1
                 else sum(col1) over (order by col1 desc rows between 1
     preceding and 1 preceding) - 1
             end as high_band ,
             col2
         from
             (select 2 (smallint) as col1,
                 200 (smallint)   as col2
             from sys_calendar.calendar
             where calendar_date = date
             union
             select 6 (smallint) as col1,
                 10 (smallint)   as col2
             from sys_calendar.calendar
             where calendar_date = date
             union
             select 9 (smallint) as col1,
                 400 (smallint)  as col2
             from sys_calendar.calendar
             where calendar_date = date
             union
             select 13 (smallint) as col1,
                 60 (smallint)    as col2
             from sys_calendar.calendar
             where calendar_date = date
             ) as sourcedata
         )
         b
     inner join sys_calendar.calendar c
         on c.day_of_calendar - 1 between b.low_band and b.high_band
     order by 1;

Kyle Prescott
UnumProvident

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