If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
identify missing numbers in sequence of records
Please can someone tell me how to identify gaps in supposedly sequential data
and calculate the value of teh difference. Thanks. |
#2
|
|||
|
|||
identify missing numbers in sequence of records
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 "Tom Whyte" wrote in message ... Please can someone tell me how to identify gaps in supposedly sequential data and calculate the value of teh difference. Thanks. |
#3
|
|||
|
|||
identify missing numbers in sequence of records
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; "Tom Whyte" wrote: Please can someone tell me how to identify gaps in supposedly sequential data and calculate the value of teh difference. Thanks. |
#4
|
|||
|
|||
identify missing numbers in sequence of records
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' "KARL DEWEY" wrote: 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; "Tom Whyte" wrote: Please can someone tell me how to identify gaps in supposedly sequential data and calculate the value of teh difference. Thanks. |
#5
|
|||
|
|||
identify missing numbers in sequence of records
Tom Whyte wrote:
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/AwUBSlZQnoechKqOuFEgEQIy0QCgjkq8fUZbxVef3n1LgulrjW YbPQYAoK5L Ol9OsAFUps4yyDB+PQL/J+iK =jhwq -----END PGP SIGNATURE----- |
Thread Tools | |
Display Modes | |
|
|