>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