Finding breaks in sequence

[crystalnewbie]

Got a table with a location, recnum fields among other things. The data looks like:

recnum location revenue
1001 CA 100
1002 CA 120
1005 CA 200
1006 CA 300
1001 HI 202
1003 HI 200
1004 HI 100

Would like to find the breaks in recnum and output it as such:

recnum location
1003 CA
1004 CA
1002 HI

How would I go about doing this?

TIA--

 

[JohnDTampaBay]

See faq183-840

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[TheJon]

Is there a constant number of values for recnum for all values of location? If so, create a list of distinct recnum / location values from a cross join and compare this with a key made of the two values in the main table, like this.

create table myRecnum(recnum int)
create table myLocation(location varchar(2))
insert into myRecnum(recnum)
values(1001)
insert into myRecnum(recnum)
values(1002)
insert into myRecnum(recnum)
values(1003)
insert into myRecnum(recnum)
values(1004)
insert into myRecnum(recnum)
values(1005)
insert into myRecnum(recnum)
values(1006)
-- repeat for all recnums

insert into myLocation(location)
values('CA')
insert into myLocation(location)
values('HI')
-- repeat for all locations

select
*
from
  myRecnum b
    cross join
  myLocation c
where
  convert(varchar, recnum) + location not in (select convert(varchar, recnum) + location from myTable)

Otherwise if you can guarantee that there is always a first and last record for each location / recnum permutation, then use a cursor to go through the records, with a while loop matching recnum to an internal counter, resetting the internal counter when the location changes.

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]