identify missing numbers in sequence of records

[Tom Whyte]

Please can someone tell me how to identify gaps in supposedly sequential data
and calculate the value of teh difference. Thanks.

 

[vanderghast]

Rank the data, then subtract the value from the rank. GROUP on that
difference, finding the MIN (start of un-interrupted sequence) and MAX (end
of un-interrupted sequence) for each of these groups:


SELECT MIN(valueField) As start, MAX(valueField) AS ending
FROM somequery
GROUP BY valueField-Rank



To get the rank, many possibilities. Here is one (which will be the
'somequery' you will use here up) :

SELECT a.valueField, COUNT(*) AS rank
FROM table AS a INNER JOIN table AS b
ON a.valueField >=b.valueField
GROUP BY a.valueField



As example, with:

Table
valueField
1
2
3
5
6
7
8
9
11
12



The somequery (the last one) return:


valueField rank
1 1
2 2
3 3
5 4
6 5
7 6
8 7
9 8
11 9
12 10


And the first query will compute valueField-rank, and group on these
differences:

valueField rank
1 1 0
2 2 0
3 3 0
5 4 1
6 5 1
7 6 1
8 7 1
9 8 1
11 9 2
12 10 2



returning
start ending
1 3
5 9
11 12


since the un-interrupted sequences are from 1 to 3, 5 to 9, and 11 to 12



Vanderghast, Access MVP

 

[KARL DEWEY]

I got this from here before and it works. It is slow if you have many
thousands of records.
Table tblListOfNumbers has field ID as autonumber and MyList as integer
field with the number sequences to be checked.

SELECT (SELECT A.MyList + 1 FROM tblListOfNumbers AS A WHERE A.ID = (SELECT
A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID = tblListOfNumbers.ID + 1 AND
A.MyList <> tblListOfNumbers.MyList + 1)) & ' to ' & (SELECT A.MyList - 1
FROM tblListOfNumbers AS A WHERE A.ID = (SELECT A.ID FROM tblListOfNumbers
As A WHERE A.ID = tblListOfNumbers.ID + 1)) AS MissingRange
FROM tblListOfNumbers
WHERE (SELECT A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1 AND A.MyList <> tblListOfNumbers.MyList + 1) IS NOT
NULL;

 

[KARL DEWEY]

The list needs to be in ascending along with the ID as below --
ID MyList
1 1
2 2
3 3
4 6
5 7
6 8
7 9
8 11

It will result in '4 to 5' and '10 to 10'

 

[MGFoster]

Please can someone tell me how to identify gaps in supposedly sequential data
and calculate the value of teh difference. Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I found this solution on an SQL Server forum (translated to JET SQL):

SELECT last_nbr
, next_nbr
, last_nbr + 1 As first_avail
, next_nbr - 1 As last_avail
, next_nbr - (last_nbr + 1) As total_nbrs
FROM (SELECT
(SELECT NZ(Max(Seq2.SeqNumber),0) As SeqNumber
FROM SequenceTable Seq2
WHERE Seq2.SeqNumber < Seq1.SeqNumber) As last_nbr
, SeqNumber As next_nbr
FROM SequenceTable Seq1) as A
WHERE next_nbr - last_nbr > 1
ORDER BY last_nbr

Output example:

last_nbr next_nbr first_avail last_avail total_nbrs
0 10 1 9 9
103 105 104 104 1
107 114 108 113 6
116 129 117 128 12


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlZQnoechKqOuFEgEQIy0QCgjkq8fUZbxVef3n1LgulrjWYbPQYAoK5L
Ol9OsAFUps4yyDB+PQL/J+iK
=jhwq
-----END PGP SIGNATURE-----