>>Anybody got any spiffy tricks to find the gaps in a range using SQL?
>>
>>Say I've got a table with:
>>
>>

>>Start   End
>>    1     5
>>    6     8
>>   12    15
>>   19    24
>>

>>
>>I'd like to find the gaps (9-11,16-18). One other thing, I'd also like to be able to give it some limits like "find the gaps between 10-17" so it should return 10-11, 16-17, in that case. Or do I have to do this by just brute force, since I need to look at 2 records at a time?
>>
>>TIA
>
>Everything is doable, but 'brute forse' will be better. Anyway, this is chain Select-SQL, you may combine them into one:
>1) Make sure that records are in physical order
>Select start,end from mytable into cursor tmp1 order by 1
>2) Get 'start' and 'end' cursors
>Select recno() as recno, start from tmp1 into cursor tmpstart
>Select recno()+1 as recno, end from tmp1 into cursor tmpend
>3) Join these cursors
>Select start-1,end+1 from tmpstart,tmpend ;
>where tmpstart.recno=tmpend.recno ;
>and start-end>1


Thanks, Ed! With just a little tweaking, I think I can make this do exactly what I want. Neat approach to the 2 record problem, too. That's what had me baffled with SQL. Thanks!