Please see this blog http://pratchev.blogspot.com/2010/02/refactoring-ranges.html



>>I have a query that I can't figure out. I need to list log delivery tickets (truck loads of trees) based on the number of tickets in a book and the min and max of the tickets that have been used and not used. Sounds simple so far. The problem is when the tickets are used out of order in the book. In a book of 25 tickets, if tickets 1,2,3,4 has been used, 5,6,7,8,9 has not been used, and 10,11,12 have been used, then I need the following list.
>>

>>Ticket book status    Min ticket   Max ticket
>>    1               Y        1                4
>>    1               N        5                9
>>    1               Y       10              12
>>    1               N       13              25             
>>

>>The query has the following list.
>>

>>select booknumber,status, min(ticket),max(ticket) from tickets
>>group by booknumber,status
>>
>>Ticket book status    Min ticket   Max ticket
>>    1               Y        1                12
>>    1               N        5                25
>>

>>
>>Any way to do this in a query?
>>
>>Thanks.
>
>

>*	In a book of 25 tickets, 
>*	if tickets 1,2,3,4 has been used, 
>*	5,6,7,8,9 has not been used, 
>*	and 10,11,12 have been used, 
>*	then I need the following list. 
>
>CREATE CURSOR tickets (booknumber I , status C, ticket I)
>FOR K=1 TO 4
>	INSERT INTO tickets VALUES (1,'Y',m.k)
>NEXT 
>FOR K=K TO 9
>	INSERT INTO tickets VALUES (1,'N',m.k)
>NEXT 
>FOR K=K TO 12
>	INSERT INTO tickets VALUES (1,'Y',m.k)
>NEXT
>
>Topticket = 25
>
>SELECT	booknumber	;
>,		MIN(status)	status 		;
>,		MIN(ticket)	MinTicket	;
>,		MAX(ticket)	MaxTicket	;
>FROM;
>(	SELECT T1.*;
>	,	(SELECT MIN(ticket)	FROM tickets;
>				WHERE booknumber = T1.booknumber ;
>				AND status <> T1.status ;
>				AND ticket > T1.ticket) AS groupId ;
>FROM tickets T1 ) X;
>GROUP BY booknumber,groupId;
>UNION ALL;
>SELECT booknumber ;
>,'N';
>,MAX(ticket)+1;
>,m.Topticket;
>FROM tickets;
>GROUP BY booknumber;
>HAVING MAX(ticket) < m.Topticket;
>ORDER BY 1,3
>