|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
(SELECT 1 AS Expr1
FROM dbo.GEPICS_Data AS b
WHERE (CSN = a.CSN + 1)))
ORDER BY 'Missing CSNs'
Any help would be appreciated.