Finding Gaps in a Sequential Number Sequence
-
Dear Sir,
The query what u have posted helped me exactly the way i want the result. But i am completeley unaware of how it get executed. Kindly help.
-
n
-
Hey!Mike Arney this is a good try. I have tried various algorithms in my experience and have never come across anything as efficient as this. However, the Math behind this was not adding up for me.
Therefore i went to http://mathforum.org/dr.math/ & Thanks To Dr Peterson who confirmed that this formula is invalid.
Try to use your algorithm for any of the below sequence.
1, 14
Or, if you prefer,
1, 3, 5, 6
Cheers!
Chinkit
-
-
- naumon765 wrote:
This would be a lot more useful if it were in a code block instead of a graphic. And, yeah, code blocks preserve leading spaces for indentation/formatting.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
hi Jeff Here it is
-- 1. LEAD/LAG (Window Function)
SELECT
StartGap = id,
EndGap = LEAD(id) OVER (ORDER BY id),
FirstAvailable = id + 1,
LastAvailable = LEAD(id) OVER (ORDER BY id) - 1,
AvailableCount = LEAD(id) OVER (ORDER BY id) - id - 1
FROM dbo.Numbers
WHERE LEAD(id) OVER (ORDER BY id) - id > 1
ORDER BY id;
-- 2. Self-Join
SELECT
StartGap = a.id,
EndGap = b.id,
FirstAvailable = a.id + 1,
LastAvailable = b.id - 1,
AvailableCount = b.id - a.id - 1
FROM dbo.Numbers a
JOIN dbo.Numbers b
ON b.id = (SELECT MIN(id) FROM dbo.Numbers WHERE id > a.id)
WHERE b.id - a.id > 1
ORDER BY a.id;
-- 3. Correlated Subquery
SELECT
StartGap = (SELECT MAX(id) FROM dbo.Numbers WHERE id < a.id),
EndGap = a.id,
FirstAvailable = (SELECT MAX(id) FROM dbo.Numbers WHERE id < a.id) + 1,
LastAvailable = a.id - 1,
AvailableCount = a.id - ((SELECT MAX(id) FROM dbo.Numbers WHERE id < a.id) + 1)
FROM dbo.Numbers a
WHERE a.id - (SELECT MAX(id) FROM dbo.Numbers WHERE id < a.id) > 1
ORDER BY a.id;
-- 4. Recursive CTE (List All Missing Numbers)
WITH Seq AS (
SELECT MIN(id) AS StartId, MAX(id) AS EndId FROM dbo.Numbers
),
AllNums AS (
SELECT StartId AS n FROM Seq
UNION ALL
SELECT n + 1 FROM AllNums, Seq WHERE n + 1 <= (SELECT EndId FROM Seq)
)
SELECT n AS MissingNumber
FROM AllNums
WHERE n NOT IN (SELECT id FROM dbo.Numbers)
OPTION (MAXRECURSION 0);
-- 5. Numbers/Calendar Table
SELECT
n.Number AS MissingNumber
FROM dbo.NumbersTable n
LEFT JOIN dbo.Numbers a ON n.Number = a.id
WHERE a.id IS NULL
AND n.Number BETWEEN (SELECT MIN(id) FROM dbo.Numbers)
AND (SELECT MAX(id) FROM dbo.Numbers)
ORDER BY n.Number;
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply