Archives of the TeradataForum

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

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

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>>