Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 16 Jun 2003 @ 15:11:34 GMT

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

Subj:   Re: Consecutive sequence sql logic
From:   Kyle Prescott

This should work. Assign the sequence number to sorted input by account and phone number - rank or moving sum can sequence this. Change the character to integer on the phone_no. the first sequence number is the base number. The fifth sequence number should be your base number + 4.

select acct
       ,max(case when a.sequence = 1 then phone_no else 0 end) as phone_1
       ,max(case when a.sequence = 5 then phone_no else 0 end) as phone_5
from (select acct
            ,substr(phone_no,1,3) || '0' || substr(phone_no,5,4) (integer)
            ,rank() over (partition by acct
                             order by acct, phone_no)
        from mytable ) a (acct,phone_no, sequence)
group by 1
having phone_5 = phone_1 + 4

  <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: 27 Dec 2016