Archives of the TeradataForum

Message Posted: Mon, 16 Jun 2003 @ 14:57:50 GMT

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

Here ya go....

1st I created a table with your values in it...

CREATE TABLE JoeTemp
(acctno   VARCHAR(20),
 telno      VARCHAR(10))

Loaded in the sample. (insert)

Here is the sub-query and it's results...

sel acctno, substr(telno,index(telno,'-')+1,4) (SMALLINT) as telnolast4,
      mdiff(telnolast4,1,telno) as mydiff,
      rank(telno asc) as myrank
from joetemp
group by 1
order by acctno, telno;

        acctno  telnolast4      mydiff  myrank
        121212  1,212           ?               1
        121212  1,213           1               2
        121212  1,214           1               3
        121212  1,215           1               4
        121212  1,216           1               5
        921212  1,111           ?               1
        921212  4,444           3,333           2

Final query...

sel distinct acctno from(
sel acctno, substr(telno,index(telno,'-')+1,4) (SMALLINT) as telnolast4,
      mdiff(telnolast4,1,telno) as mydiff,
      rank(telno asc) as myrank
from joetemp
group by 1) as t
where mydiff = 1
and myrank >= 5;

        acctno
        121212

Hope this helps!

Best Regards,

Joe Fuller
Sr Data Warehouse Consultant, DBA
Teradata Professional Services

Advanced Teradata Certified Professional
Teradata Certified Designer

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