Archives of the TeradataForum

Message Posted: Mon, 16 Jun 2003 @ 14:54:39 GMT

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

Bruce

See below - if I understand your problem correctly, this will do it. I assume your phone number is a numeric. If not, it will require some casting.

Regards

Frank

 select *

 from
        (select
                acc_no
        from
                (select
                        acc_no
                        , phone

                        /* works out the difference between the phone number and the previous  one. */
                        /* note the group by later in this derived table */
                        , case  when mdiff(phone , 1, acc_no asc, phone asc) is null
                                        then 1
                          else  mdiff(phone , 1, acc_no asc, phone asc)
                          end   (named sequence)

                        /* used to determine how many are in the sequence */
                        , csum(1, acc_no asc , phone asc)

                from    phones
                group by acc_no
                qualify sequence = 1)

                phones_temp  (acc_no, phone, seq, counter)

                group by 1
                having max(counter) >= 5
                /* at least 5 in the sequence */)

                candidate (acc_no)

 inner join
        phones p
 on     p.acc_no = candidate.acc_no;

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