Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Help on SQL query
 
From:   Victor Sokovin

  I have a table with a column as integer, I want to identify set of series from that column. Say values are 9000001-9002000 where some numbers are missing.  


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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023