SUM of Missing Numbers in a Sequence

Giganews Newsgroups
Subject: SUM of Missing Numbers in a Sequence
Posted by:  JMac (jsvic…@gmail.com)
Date: Wed, 5 Oct 2011

Right now I'm using the query below to find missing numbers in a
sequence. I'd like to create a new query that returns the SUM of how
many are missing. ie: if numbers 4, 8, 10 are missing, I'd like the
result to be 3.

SELECT    TOP (100) PERCENT CSN + 1 AS [Missing CSNs]
FROM        dbo.GEPICS_Data AS a
WHERE    (CSN NOT IN
                          (SELECT    MAX(CSN) AS Expr1
                            FROM          dbo.GEPICS_Data AS c)) AND
(NOT EXISTS
                          (SELECT    1 AS Expr1
                            FROM          dbo.GEPICS_Data AS b
                            WHERE      (CSN = a.CSN + 1)))
ORDER BY 'Missing CSNs'

Any help would be appreciated.

Thank-you

Replies