Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Finding Gaps in a Sequential Number Sequence Expand / Collapse
Author
Message
Posted Tuesday, January 5, 2010 8:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 23, 2011 8:25 PM
Points: 7, Visits: 33
Thanks for your all excellent solutions. Much appreciated!
Post #842529
Posted Saturday, July 2, 2011 11:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 5, 2013 1:45 AM
Points: 3, Visits: 23
Mike Arney (4/3/2006)


If you just need to identify IF a list of numbers is in sequence, but not where the gaps are, I believe you can do it in a single pass of the table. (Warning -- to understand this algorithm, some high school math is required!)<FONT color=#008000 size=2>



Or some grade school one:

SELECT CASE WHEN COUNT(Id) = MAX(Id) - MIN(Id) + 1 THEN 'In sequence' ELSE 'Not sequence' END FROM yourtable

Post #1135534
Posted Saturday, July 2, 2011 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 5, 2013 1:45 AM
Points: 3, Visits: 23
Here is another thing i found and modified a little.
Takes under a second for 1 millon rows and returns gaps size sorted

Change tables and fields surrounded by []
----------------------------------------

SELECT
LastSysId + 1 AS GapFrom,
NextSysId - 1 AS GapTo,
NextSysId - (LastSysId + 1) AS GapSize
FROM
(
SELECT
(
SELECT TOP 1
[SysId]
FROM
[yourtable]
WHERE
[SysId] < a.[SysId]
ORDER BY
[SysId] DESC
) AS LastSysId,
a.[SysId] AS NextSysId
FROM
[yourtable] AS a
LEFT JOIN
[yourtable] AS b ON a.[SysId] = b.[SysId] + 1
WHERE
b.[SysId] IS NULL
) AS a
WHERE
LastSysId IS NOT NULL
ORDER BY
3 DESC
Post #1135537
Posted Saturday, July 2, 2011 10:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:38 PM
Points: 38,013, Visits: 34,938
Martin Grape (7/2/2011)
Here is another thing i found and modified a little.
Takes under a second for 1 millon rows and returns gaps size sorted

Change tables and fields surrounded by []
----------------------------------------

SELECT
LastSysId + 1 AS GapFrom,
NextSysId - 1 AS GapTo,
NextSysId - (LastSysId + 1) AS GapSize
FROM
(
SELECT
(
SELECT TOP 1
[SysId]
FROM
[yourtable]
WHERE
[SysId] < a.[SysId]
ORDER BY
[SysId] DESC
) AS LastSysId,
a.[SysId] AS NextSysId
FROM
[yourtable] AS a
LEFT JOIN
[kicks_medlem_20110630] AS b ON a.[SysId] = b.[SysId] + 1
WHERE
b.[SysId] IS NULL
) AS a
WHERE
LastSysId IS NOT NULL
ORDER BY
3 DESC



Nicely done and I can verify the speed. The only problem is that the code doesn't recognize missing "SysID" of 1. In fact, it doesn't recognize any gap from 1 to x-1 if all the rows between 1 to x-1 are missing. That could be OK for some folks. For me, it's usually not. It's neither wrong nor right. "It Depends" on what it's being used for.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1135582
Posted Sunday, July 3, 2011 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 5, 2013 1:45 AM
Points: 3, Visits: 23
@Jeff

Here is a little stored procedure i made out of this that will return a list of gaps including missing 1-x (or 0-x or whatever-x) or
return the first available number i a sequence. Njoy


-- Drop procedure if it exists
IF EXISTS (SELECT * FROM [sys].[objects] WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[FindGap]') AND [type] IN (N'P', N'PC')) DROP PROCEDURE [dbo].[FindGap]
GO

-- Create procedure
CREATE PROCEDURE [dbo].[FindGap]
@strFindGapTable VARCHAR(254),
@strFindGapField VARCHAR(254),
@binFindGapLower BIGINT = 1,
@intFindGapMode INT = 0
AS
-- +---------------------------------------------------------------------------+
-- | |
-- | Type : SQL Procedure |
-- | Name : FindGap |
-- | Version : 1.1 |
-- | Creator : Martin Grape, ActionBase, +46 73 391 88 89 |
-- | Info : Returns first available numer in a field of sequence or |
-- | : displays a list of available gaps and number of gaps. |
-- | Input : @strFindGapTable -> Name of table to run on |
-- | : @strFindGapField -> Name of field |
-- | : @strFindGapLower -> Lower value to find gap from |
-- | : @strFindGapMode -> 0=All gaps, 1=Next available number |
-- | Example : EXEC FindGap 'yourtable', 'sysid', 1, 1 |
-- | History : 2011-07-03 Created v1.0 |
-- | : 2011-07-03 Added check for all numbers in sequence v1.1 |
-- | : |
-- | |
-- +---------------------------------------------------------------------------+

-- Declare variables
DECLARE @strFindGapSQL AS NVARCHAR(2000);
DECLARE @strFindGapPar AS NVARCHAR(2000);
DECLARE @binFindGapMin AS BIGINT;
DECLARE @binFindGapMax AS BIGINT;
DECLARE @binFindGapCount AS BIGINT;
DECLARE @binFindGapFirst AS BIGINT;

-- Make SQL to find min and max values
SET @strFindGapSQL = N'SELECT @binMinVal = MIN(' + @strFindGapField + '), @binMaxVal = MAX(' + @strFindGapField + '), @binCount = COUNT(*) FROM ' + @strFindGapTable;
SET @strFindGapPar = N'@binMinVal BIGINT OUTPUT, @binMaxVal BIGINT OUTPUT, @binCount BIGINT OUTPUT';

-- Get values into variables
EXEC SP_EXECUTESQL @strFindGapSQL, @strFindGapPar, @binFindGapMin OUTPUT, @binFindGapMax OUTPUT, @binFindGapCount OUTPUT;

-- Check mode
IF @intFindGapMode = 1
-- Next available sequence number
BEGIN
-- Check if min is bigger than supplied lower, then take that value
IF @binFindGapMin > @binFindGapLower
BEGIN
RETURN @binFindGapLower;
END
-- Check if max is smaller than supplied lower, then take that value
ELSE IF @binFindGapMax < @binFindGapLower
BEGIN
RETURN @binFindGapLower;
END
-- Check if max is the same as supplied lower, then return + 1
ELSE IF @binFindGapMax = @binFindGapLower
BEGIN
RETURN @binFindGapLower + 1;
END
-- Check if no existing gaps, return higest + 1
ELSE IF @binFindGapCount = @binFindGapMax - @binFindGapMin + 1
BEGIN
RETURN @binFindGapMax + 1
END
-- Get first available number
ELSE
BEGIN
-- Create SQL to get next value
SET @strFindGapSQL = N'SELECT TOP 1 @binMinVal = LastSysId + 1 FROM (SELECT (SELECT TOP 1 ' + @strFindGapField + ' FROM ' + @strFindGapTable + ' WHERE ' + @strFindGapField + ' < a.' + @strFindGapField + ' ORDER BY ' + @strFindGapField + ' DESC) AS LastSysId, a.' + @strFindGapField + ' AS NextSysId FROM ' + @strFindGapTable + ' AS a LEFT JOIN ' + @strFindGapTable + ' AS b ON a.' + @strFindGapField + ' = b.' + @strFindGapField + ' + 1 WHERE b.' + @strFindGapField + ' IS NULL) AS a WHERE LastSysId IS NOT NULL ORDER BY 1';
SET @strFindGapPar = N'@binMinVal BIGINT OUTPUT';
-- Get value into variable
EXEC SP_EXECUTESQL @strFindGapSQL, @strFindGapPar, @binFindGapFirst OUTPUT;
-- Return value
RETURN @binFindGapFirst;
END
END
ELSE
-- All available sequence numbers as a list
BEGIN
-- Check if first value is bigger than supplied lower, then add a row
IF @binFindGapLower < @binFindGapMin
BEGIN
-- Add a union from supplied lower to the first actual value
SET @strFindGapSQL = N'SELECT ' + CAST(@binFindGapLower AS NVARCHAR) + ' AS GapFrom, ' + CAST(@binFindGapMin - 1 AS NVARCHAR) + ' AS GapTo, ' + CAST(@binFindGapMin - @binFindGapLower AS NVARCHAR) + ' AS GapSize UNION ';
END
ELSE
BEGIN
-- Reset variable
SET @strFindGapSQL = '';
END
-- Check if to add
SET @strFindGapSQL = @strFindGapSQL + 'SELECT LastSysId + 1 AS GapFrom, NextSysId - 1 AS GapTo, NextSysId - (LastSysId + 1) AS GapSize FROM (SELECT (SELECT TOP 1 ' + @strFindGapField + ' FROM ' + @strFindGapTable + ' WHERE ' + @strFindGapField + ' < a.' + @strFindGapField + ' ORDER BY ' + @strFindGapField + ' DESC) AS LastSysId, a.' + @strFindGapField + ' AS NextSysId FROM ' + @strFindGapTable + ' AS a LEFT JOIN ' + @strFindGapTable + ' AS b ON a.' + @strFindGapField + ' = b.SysId + 1 WHERE b.' + @strFindGapField + ' IS NULL) AS a WHERE LastSysId IS NOT NULL ORDER BY 1';
-- Run the select
EXEC (@strFindGapSQL);
-- Return also the rowcount
RETURN @@ROWCOUNT;
END
-- +------------------+
-- | END OF PROCEDURE |
-- +------------------+
GO

-- +---------+
-- | Testing |
-- +---------+

-- Drop test table if it existst
IF EXISTS (SELECT * FROM [sys].[objects] WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[yourtable]') AND [type] IN (N'U')) DROP TABLE [dbo].[yourtable]
GO


-- Add dummy data
SELECT 2 AS SysId INTO yourtable UNION
SELECT 6 AS SysId UNION
SELECT 8 AS SysId UNION
SELECT 9 AS SysId UNION
SELECT 11 AS SysId;


-- Example 1: Adds a row with next available number starting on 0 , displays the result table and selects next available number
DECLARE @binFindGapReturn BIGINT;
EXEC @binFindGapReturn = FindGap 'yourtable', 'sysid', 0, 1;
INSERT INTO yourtable (SysId) SELECT @binFindGapReturn
SELECT * FROM yourtable ORDER BY 1
EXEC @binFindGapReturn = FindGap 'yourtable', 'sysid', 0, 1;
SELECT @binFindGapReturn AS NextValue;
GO


-- Example 2: Returns all the gaps starting on 1 as a resultset and returns number of gaps
DECLARE @binFindGapReturn BIGINT;
EXEC @binFindGapReturn = FindGap 'yourtable', 'sysid', 1, 2;
SELECT @binFindGapReturn AS NoOfGaps;
GO

-- Example 3: Fill missing sequences to get max + 1
INSERT INTO yourtable SELECT 1
INSERT INTO yourtable SELECT 3
INSERT INTO yourtable SELECT 4
INSERT INTO yourtable SELECT 5
INSERT INTO yourtable SELECT 7
INSERT INTO yourtable SELECT 10
DECLARE @binFindGapReturn BIGINT;
EXEC @binFindGapReturn = FindGap 'yourtable', 'sysid', 0, 1;
SELECT @binFindGapReturn AS NextValue;
GO


-- Drop test table if it existst
IF EXISTS (SELECT * FROM [sys].[objects] WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[yourtable]') AND [type] IN (N'U')) DROP TABLE [dbo].[yourtable]
GO




  Post Attachments 
FindGap.sql.txt (18 views, 7.35 KB)
Post #1135616
Posted Tuesday, September 11, 2012 5:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 3:37 AM
Points: 1, Visits: 2
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.
Post #1357319
Posted Thursday, August 29, 2013 4:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 19, 2015 2:15 PM
Points: 1, Visits: 69
n
Post #1489950
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse