## Message Posted: Mon, 16 Jun 2003 @ 20:02:50 GMT

 < Last>>

 Subj: Re: Consecutive sequence sql logic From: Dieter Nöth

Burton, Bruce wrote:

 I'm trying to find phone numbers that exist on a given account that have at least 5 consecutive phone numbers in a sequence. From the below example the 1st five phone numbers would meet the criteria and the distinct account would be selected for further processing. The 2nd account would not be selected because the phone number is not in sequence or does not have at least 5 consec. sequenced numbers:

```acct         phone

121212       555-1212
121212       555-1213
121212       555-1214
121212       555-1215
121212       555-1216

921212       555-1111
921212       555-4444
```
 Any ideas on how to convert this logic into SQL?

Calculating runs and sequences with traditional SQL techniques is rather inefficient, just look at advanced SQL book like Joe Celko's "SQL for Smarties".

But with OLAP functions it's quite fast. I modified a puzzle i use in my advanced SQL classes ;-)

```create table p816_master as
(
select
cast(calendar_date as int) / 10 as acct
,trim(calendar_date (format 'yyyymmdd')) as mobile
,1 as bus_ind
,'A' as bus_seg
from
sys_calendar.calendar
sample 20000
) with data;

select
acct
,count(*) as consecutive_numbers
,min(mobile) as "From"
,max(mobile) as "To"
from
(
select
acct
,mobile
,diff
/** this is the trick:
calculate groups of rows, so consecutive rows with a diff of 0
will be part of the same datagroup **/
,sum(diff) over (partition by acct
order by mobile
rows unbounded preceding) as datagroup
from
(
select
acct
,mobile
/** moving difference - 1 between two numbers,
so consecutive rows have a diff of 0 **/
,coalesce(mdiff(cast(mobile as int), 1, mobile)-1, 0) as diff
from p816_master
where bus_ind=1
and bus_seg ='A'
group by acct
) dt
) dt
group by acct, datagroup
having count(*) >=5
order by 1,2
;
```

Dieter

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2003 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback