Identify sequential or nearly sequential records

Question

I have a column named invoice in a table which contains the invoice numbers records.

I want to identify the invoices where invoice number are sequential or nearly sequential.

Sequential is 1,2,3,4

Nearly sequential is difference of 1 or 2

And I have invoice number records as

123
124
126
128
129
133

NOTE : Data type of invoice column is nvarchar and it contains alpha numeric values as well and we have to find only numeric sequential records

Answer 1

Only for the InvNo that are numeric?

It's possible to use a WHERE [NOT] EXISTS for that.

Example Snippet:

IF OBJECT_ID ('tempdb..#SemiSequenceTest') IS NOT NULL 
    DROP TABLE #SemiSequenceTest;

CREATE TABLE #SemiSequenceTest
(
  id INT IDENTITY(1,1) PRIMARY KEY,
  InvNo NVARCHAR(30) NOT NULL
);

INSERT INTO #SemiSequenceTest (InvNo) VALUES
(N'123'), (N'124'), (N'126'), 
(N'130'), (N'131'), (N'135'),
(N'FOO123'), (N'FOO124'), (N'BAR136');

SELECT *
FROM #SemiSequenceTest  t
WHERE t.InvNo LIKE '[0-9]%[0-9]'
  AND ISNUMERIC(t.InvNo) = 1
  AND EXISTS
  (
      SELECT 1
      FROM #SemiSequenceTest t2
      WHERE t2.InvNo LIKE '[0-9]%[0-9]'
        AND ISNUMERIC(t2.InvNo) = 1
        AND ABS(TRY_CAST(t2.InvNo AS INT) - TRY_CAST(t.InvNo AS INT)) BETWEEN 1 AND 2
 );

Returns:

id  InvNo
1   123
2   124
3   126
4   130
5   131

But using the window functions works also.
For examply by using LAG and LEAD.

Then the query would look like this:

SELECT Id, InvNo
FROM
(
    SELECT Id, InvNo
    , CAST(InvNo AS INT) AS InvNum
    , CAST(LAG(InvNo) OVER (ORDER BY CAST(InvNo AS INT) ASC) AS INT) AS prevInvNum
    , CAST(LEAD(InvNo) OVER (ORDER BY CAST(InvNo AS INT) ASC) AS INT) AS nextInvNum
    FROM #SemiSequenceTest  t
    WHERE t.InvNo LIKE '[0-9]%[0-9]'
      AND ISNUMERIC(t.InvNo) = 1
) q
WHERE (InvNum <= prevInvNum + 2 OR InvNum >= nextInvNum - 2);

Answer 2

You can try the below query, the concept is for sequential number the previous invoice + 1 should be the next one, otherwise it is not sequential.

create table #temp (InvNo int)
insert into #temp values (123), (124), (126), (128), (129), (133)

-- For sequential
SELECT
   [current].InvNo,
   [current].InvNo + 1,
   ISNULL([next].InvNo, 0) - [current].InvNo as Seq
FROM
   #temp       AS [current]
LEFT JOIN
   #temp       AS [next]
      ON [next].InvNo = (SELECT MIN(InvNo) FROM #temp WHERE InvNo > [current].InvNo)
      where [current].InvNo + 1 = ISNULL([next].InvNo, 0) 

-- For not sequential
SELECT
   [current].InvNo
FROM
   #temp       AS [current]
LEFT JOIN
   #temp       AS [next]
      ON [next].InvNo = (SELECT MIN(InvNo) FROM #temp WHERE InvNo > [current].InvNo)
      where [current].InvNo + 1 <> ISNULL([next].InvNo, 0)

The result is as shown below.