Archives of the TeradataForum

Message Posted: Tue, 14 Nov 2006 @ 19:50:50 GMT

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

Rajesh,

Try the following. The first step is intended to simulate your source table, the second determines the different start and stopping points and the third consolidates it into the different ranges.

--This is your data

     create volatile table MyData as
        (sel
         day_of_calendar as MyValue
        ,ROW_NUMBER() OVER (ORDER BY day_of_calendar) as Row_Num
     from
        sys_calendar.calendar
     where
           day_of_calendar < 10
        or day_of_calendar between 24 and 31
        or day_of_calendar between 45 and 62
        or day_of_calendar = 88
     group by 1) with data on commit preserve rows;

--Build a table that contains all the start and stopping points

     create volatile table MyRanges as
     (SELECT
        MiddleT.*
        ,CASE WHEN PriorT.Myvalue IS NOT NULL THEN 'Y'
                    ELSE 'N'
               END As PriorExists
        ,CASE WHEN AfterT.Myvalue IS NOT NULL THEN 'Y'
                    ELSE 'N'
               END As AfterExists
        ,ROW_NUMBER() OVER (ORDER BY MiddleT.MyValue) as Range_Nbr
     FROM
        MyData MiddleT
     LEFT JOIN
        MyData AfterT
     ON
        AfterT.Myvalue = MiddleT.myValue + 1
     LEFT JOIN
        MyData PriorT
     ON
        PriorT.Myvalue = MiddleT.myValue - 1
     WHERE
        AfterT.MyValue IS NULL
     OR
        PriorT.MyValue IS NULL
     ) with data on commit preserve rows;

--Consolidate the different start and stopping points into ranges

     SELECT
         ROW_NUMBER() OVER (ORDER BY MIN_Val) as Set_Nbr
        ,L.MyValue AS MIN_Val
        ,CASE
           WHEN r.priorExists = 'Y' then r.MyValue
                    ELSE L.MyValue
               END AS Max_Val
     FROM
        MyRANGES L
     LEFT JOIN
        MyRANGES R
     on
        l.Range_Nbr + 1    = r.Range_Nbr
        AND l.afterExists  = 'Y'
     WHERE
        l.priorExists      = 'N';

Johannes de Wet
UnumProvident Corportaion

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