Add a sequence check column

Question

Could you please help me out with a script to set a new column = whether the column is one more than the column above or not. I want it to check the sequence to detect missing document numbers.

I think I would then obviously need to exclude the first row?

Obviously quite simple in IDEA and Excel just not sure with SQL?

Many thanks, J

What version of SQL-server are you using, what does the table look like and what ordering are you using when you say above? — idstam, May 20 '15 at 08:05
SQL Server 2008, the table is made up of a column, a list of numerics, 101, 102 , 103. I have a column next to it that I just want to indicate True or False really, where it follows the sequence or not? — user4242750, May 20 '15 at 08:08
This question seems very similar: http://stackoverflow.com/questions/1057389/sql-query-to-find-missing-sequence-numbers — Klas Lindbäck, May 20 '15 at 08:13
@klas in comments, thanks. http://stackoverflow.com/questions/1057389/sql-query-to-find-missing-sequence-numbers — user4242750, May 20 '15 at 08:18
@KlasLindbäck you linked to a sqlserver 2005 question from 2009. Many things have changed since then — t-clausen.dk, May 20 '15 at 08:54
@t-clausen.dk It's the same question. However, since it lacks an accepted answer and others are asking which version I figured there might be better ways to solve it now, hence I didn't mark it as a dupe. If this question gets a good answer (maybe even with different solutions for different versions) we should consider making the other question a duplicate of this one. — Klas Lindbäck, May 20 '15 at 09:05

t-clausen.dk · Answer 1 · 2015-05-20T09:40:33.460

The link in the comment from Klas Lindbäck is refering to sqlserver 2005. In sqlserver 2012 you can use LAG

;WITH CTE AS
(
  SELECT 
    CASE WHEN 
      coalesce(LAG(a) over (order by a), 0) + 1 = a 
    THEN 1 ELSE 0 END SequenceIntact, 
    a
  FROM (values(1),(3),(5),(6),(7)) t(a)
)
SELECT 
  SequenceIntact, 
  a
FROM cte

Edit: Since you are using sqlserver 2008, you can't use LAG, but you can use row_number - I used dense_rank to compensate for duplicates:

;WITH CTE as
(
  SELECT 
      a - dense_rank() over (order by a) grp,
      a
  FROM (values(1),(3),(5),(6),(7)) t(a)
)
SELECT 
  CASE WHEN min(a) over (partition by grp) = a 
       and a > 1 -- allowing first value to have SequenceIntact
       THEN 0 ELSE 1 END SequenceIntact,
  a
FROM CTE

Result:

SequenceIntact a
1              1
0              3
0              5
1              6
1              7

score 0 · Accepted Answer · answered May 20 '15 at 09:46

If you want just to check column for gaps then:

DECLARE @t TABLE ( id INT)

INSERT INTO @t VALUES(1),(2),(4),(5),(8),(13),(14)

SELECT * FROM @t t1
LEFT JOIN @t t2 ON t2.id + 1 = t1.id
WHERE t2.id IS NULL

Output:

id  id
1   NULL
4   NULL
8   NULL
13  NULL

That means that these values break the sequence.

Add a sequence check column

2 Answers2