On 8/18/05, SGreen@stripped <SGreen@stripped> wrote:
>
>
> Peter,
>
> Your query may work for data with single-row gaps (like his example data)
> but it will not work if the sequence skips more than one number.
>
> Look at this sequence: 1,2,3,8,9,10
>
> The OP would like to detect that 4,5,6, and 7 are missing from the
> sequence. Your query would have only found that 7 was missing.
>
> Nice try, but sorry. It just won't meet the need.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> Peter Brawley <peter.brawley@stripped> wrote on 08/18/2005 10:56:34
> AM:
>
> > Scott,
> >
> > >How do I execute a query that shows missing ID's like so:
> >
> > SELECT id AS i
> > FROM tbl
> > WHERE i <> 1 AND NOT EXISTS(
> > SELECT id FROM tbl WHERE id = i - 1
> > );
> >
> > PB
> >
> > -----
> >
> > Scott Hamm wrote:
>
> > If I got a table as follows:
> >
> >
> > ID foo
> > 1 12345
> > 2 12346
> > 4 12348
> > 6 12349
> > 7 12388
> > 9 12390
> > How do I execute a query that shows missing ID's like so:
> >
> > 3
> > 5
> > 8
> >
> > I wouldn't expect for it to show deleted data that was deleted, just
> show
> > the "skipped" ID's.
> >
> > That way I determine if operator deleted too much (cheating at QC)
> >
> > Is it possible?
> >
> >
> >
> > No virus found in this incoming message.
> > Checked by AVG Anti-Virus.
> > Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date:
> 8/17/2005
> > No virus found in this outgoing message.
> > Checked by AVG Anti-Virus.
> > Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date:
> 8/17/2005
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
Something similiar to this query for MS SQL (very time consuming)
SET NOCOUNT ON
DECLARE
@mindate smalldatetime,
@maxdate smalldatetime
SET @mindate='2005-08-01'
SET @maxdate='2005-08-31'
CREATE TABLE
#count (ID int);
DECLARE
@low int,
@high int
SET
@low=(
SELECT
MIN(QAErrors.ID)-1
FROM
QAErrors
LEFT JOIN
QA Q
ON Q.ID=QAErrors.QAID
WHERE
KeyDate
BETWEEN
@mindate
AND
@maxdate
)
SET
@high=(
SELECT
MAX(QAErrors.ID)
FROM
QAErrors
LEFT JOIN
QA Q
ON
Q.ID=QAErrors.QAID
WHERE
KeyDate
BETWEEN
@mindate
AND @maxdate
)
DECLARE
@counter INT
SET
@counter = @low
WHILE
@counter < @high
BEGIN
SET
@counter = @counter + 1
INSERT INTO
#count
VALUES
(@counter)
END
SET NOCOUNT OFF
SELECT
@mindate AS 'From',
@maxdate AS 'To',
count(*) AS 'Total Deleted'
FROM
#count C
LEFT JOIN
QAErrors QE
ON
QE.ID=C.ID
LEFT JOIN
QA Q
ON
Q.ID=QE.QAID
WHERE
Q.ID <http://Q.ID> is null;
DROP TABLE
#count;
--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
