Archives of the TeradataForum

Message Posted: Tue, 14 Nov 2006 @ 20:00:37 GMT

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

With your permission, I'll slightly simplify the data:

     ct seq_tb (seq_id int);
     ins seq_tb values(1);
     ins seq_tb values(2);
     ins seq_tb values(3);
     ins seq_tb values(10);
     ins seq_tb values(11);
     ins seq_tb values(20);
     ins seq_tb values(21);
     ins seq_tb values(22);

The the following query seems to return what you were looking for:

     sel DT1.LOC_MIN, DT2.LOC_MAX
     from
     (
     sel ROW_NUMBER() OVER (ORDER BY SEQ_ID ASC) as RN, CASE WHEN LOWER_BAND = 0
     then SEQ_ID END AS LOC_MIN
     FROM (sel seq_id SEQ_ID,
     CASE WHEN MAX(seq_id) OVER(ORDER BY seq_id ASC ROWS BETWEEN CURRENT ROW AND 1
     FOLLOWING) = seq_id+1 then rank() OVER( ORDER BY seq_id ASC )
     ELSE 0 END as UPPER_BAND,
     CASE WHEN MIN(seq_id) OVER(ORDER BY seq_id ASC ROWS BETWEEN 1 PRECEDING
     AND CURRENT ROW ) = seq_id-1 then rank() OVER( ORDER BY seq_id ASC )
     ELSE 0 END as LOWER_BAND
     FROM seq_tb) DT
     where LOWER_BAND=0
     ) DT1,
     (
     sel ROW_NUMBER() OVER (ORDER BY SEQ_ID ASC) as RN, CASE WHEN UPPER_BAND = 0
     then SEQ_ID END AS LOC_MAX
     FROM (sel seq_id SEQ_ID,
     CASE WHEN MAX(seq_id) OVER(ORDER BY seq_id ASC ROWS BETWEEN CURRENT ROW AND 1
     FOLLOWING) = seq_id+1 then rank() OVER( ORDER BY seq_id ASC )
     ELSE 0 END as UPPER_BAND,
     CASE WHEN MIN(seq_id) OVER(ORDER BY seq_id ASC ROWS BETWEEN 1 PRECEDING
     AND CURRENT ROW ) = seq_id-1 then rank() OVER( ORDER BY seq_id ASC )
     ELSE 0 END as LOWER_BAND
     FROM seq_tb) DT
     where UPPER_BAND=0
     ) DT2
     where DT1.RN=DT2.RN;

      *** Query completed. 3 rows found. 2 columns returned.
      *** Total elapsed time was 1 second.


     LOC_MIN      LOC_MAX
     -------  -----------
           1            3
          10           11
          20           22

Basically, the query tries to find consecutive chunks and then provides their lower and upper limits. It comes with no warranty and should just give an idea of possibilities provided by OLAP functions.

Regards,

Victor

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