Find Gaps in Sequence

[Kirk P.]

Tom Ellison posted a solution to this issue back in April '06. I'm trying to
modify it slightly to fit my needs. I've got a large table with 2 fields:
entity and entry_no. The combination of entity and entry_no describe a
unique record, and I'm looking for gaps in the entry_no sequence WITHIN
entity. I'm throwing in GROUP BY clauses in various places, but so far I
haven't gotten it to work.

Here's what I've got thus far:

SELECT entry_no+1 AS BeginMissing, (SELECT MIN(entry_no)
FROM tblSEQUENCE T1
WHERE T1.entry_no > t.entry_no)-1 AS EndMissing
FROM tblSEQUENCE AS t
WHERE (((Exists (SELECT * FROM tblSEQUENCE T1
WHERE T1.entry_no = t.entry_no + 1))=False) AND ((t.entry_no)<(SELECT
Max(entry_no) FROM tblSEQUENCE)));

This works across all entry_no's, but not for entry_no's within entity
groupings. Help!

 

[John Spencer]

Possibly the following will work. I have no way to test it.

SELECT Entity,
Entry_no+1 AS BeginMissing
, (SELECT MIN(entry_no)
FROM tblSEQUENCE T1
WHERE T1.entry_no > t.entry_no
AND T1.Entity = T.Entity)-1 AS EndMissing
FROM tblSEQUENCE AS t
WHERE Exists (SELECT *
FROM tblSEQUENCE T1
WHERE T1.Entity = T.Entity
AND T1.entry_no = t.entry_no + 1)=False
AND t.entry_no <(SELECT Max(entry_no)
FROM tblSEQUENCE as T2
WHERE T2.Entity = T.Entity)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Michel Walsh]

I prefer joins.

With

gr seq
1010 1
1010 2
1010 5
1010 6
1010 7
1011 1
1011 2
1011 3


the idea is to rank, by group:


gr seq rank
1010 1 1
1010 2 2
1010 5 3
1010 6 4
1010 7 5
1011 1 1
1011 2 2
1011 3 3



and then, compute seq - rank:

gr seq rank seq-rank
1010 1 1 0
1010 2 2 0
1010 5 3 2
1010 6 4 2
1010 7 5 2
1011 1 1 0
1011 2 2 0
1011 3 3 0



And note that each record having the same {gr, seq-rank} couple belong
to the same un-interrupted sequence.


So, to rank by gr, we can use:
------------------------------------------------------------
SELECT a.gr, a.seq, COUNT(*) AS rank
FROM table AS a INNER JOIN table AS b
ON a.gr=b.gr AND a.seq >= b.seq
GROUP BY a.gr, a.seq
-------------------------------------------------------------

saved as q1. Then

-------------------------------------------------
SELECT gr, MIN(seq), MAX(seq)
FROM q1
GROUP BY gr, seq-rank
--------------------------------------------------

returns the following result:


gr minSeq maxSeq
1010 1 2
1010 5 7
1011 1 3


ie: for 1010, 1 to 2, then 5 to 7 are used.
for 1011, 1 to 3 are used.


Sure, it supplies the USED values, not the MISSING ones. The MISSING ones
are maxSeq+1 to the next minSeq-1, same value of gr.


That solution works only if there is no dup in the initial table (ie, if
each couple {gr, seq} is unique).



Vanderghast, Access MVP

 

[KARL DEWEY]

I got these some time back and they work well --
QryMissingRange ---
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;


------- These find first and last missing in sequence -----
First missing in sequence --
SELECT MyList+1 AS Missing
FROM Maximo_Tag_Numbers AS T
WHERE (((Exists (SELECT * FROM Maximo_Tag_Numbers T1 WHERE
T1.MyList = T.MyList + 1))=False));

Last missing in sequence --
SELECT [MyList]-1 AS Missing_End
FROM Maximo_Tag_Numbers AS T
WHERE (((Exists (SELECT * FROM Maximo_Tag_Numbers T1 WHERE
T1.MyList = T.MyList - 1))=False));

 

[Kirk P.]

It appears to work exactly as needed. Thanks!

Possibly the following will work. I have no way to test it.

SELECT Entity,
Entry_no+1 AS BeginMissing
, (SELECT MIN(entry_no)
FROM tblSEQUENCE T1
WHERE T1.entry_no > t.entry_no
AND T1.Entity = T.Entity)-1 AS EndMissing
FROM tblSEQUENCE AS t
WHERE Exists (SELECT *
FROM tblSEQUENCE T1
WHERE T1.Entity = T.Entity
AND T1.entry_no = t.entry_no + 1)=False
AND t.entry_no <(SELECT Max(entry_no)
FROM tblSEQUENCE as T2
WHERE T2.Entity = T.Entity)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County