sql server mseq: List non-sequenced number -- sql server mseq

List non-sequenced number

KG6LFZ
10/30/2003 3:48:03 PM

sql server mseq:

I have Access 2000 database. In its table, there is a
field, called Case and field type is number.

Usually its data is in sequence. But sometimes, it skips
one number. I want to list the beginning of records, and
end of sequence.

For example, data is 1, 2, 3, 5, 6, 7, 8, 10, 11.
Report should list:
From 1 to 3
From 5 to 8 and
From 10 to 11.

Do you have any functions that make me do this?
I appreciate your suggestions in advance.

Thank you very much.

List non-sequenced number

Nigel Rivett
10/30/2003 5:01:07 PM

See
http://www.nigelrivett.net/FindGapsInSequence.html

[quoted text, click to view]

Re: List non-sequenced number

Vishal Parkar
10/31/2003 6:31:00 AM

Hello,

See if following helps.
--sample data
CREATE TABLE #Test
(
IDNo INT UNIQUE
)
INSERT INTO #Test VALUES(9)
INSERT INTO #Test VALUES(10)
INSERT INTO #Test VALUES(6)
INSERT INTO #Test VALUES(5)
INSERT INTO #Test VALUES(1)
INSERT INTO #Test VALUES(2)

-- query to get min number of the gap
select idno from #test where idno in
(SELECT
case when
((select min(idno) from #test where idno > a.idno)
- idno) > 1 then idno else null end
FROM #Test a)

-- query to get max number of the gap
select idno from #test where idno in
(SELECT
case when
(idno - (select max(idno) from #test where idno < a.idno)
) > 1 then idno else null end
FROM #Test a)
order by 1 asc

--
- Vishal

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.