Finding gaps, NOT EXISTS clause analysis_1_put on a wall, Sql Server

Finding gaps, NOT EXISTS clause analysis_1_put on a wall

CREATE TABLE dbo.NumSeq (seqval INT NOT NULL CONSTRAINT PK_NumSeq PRIMARY KEY ); 
INSERT INTO dbo.NumSeq(seqval) VALUES(2); 
INSERT INTO dbo.NumSeq(seqval) VALUES(3); 
INSERT INTO dbo.NumSeq(seqval) VALUES(11); 
INSERT INTO dbo.NumSeq(seqval) VALUES(12); 
INSERT INTO dbo.NumSeq(seqval) VALUES(13); 
INSERT INTO dbo.NumSeq(seqval) VALUES(31); 
INSERT INTO dbo.NumSeq(seqval) VALUES(33); 
INSERT INTO dbo.NumSeq(seqval) VALUES(34); 
INSERT INTO dbo.NumSeq(seqval) VALUES(35); 
INSERT INTO dbo.NumSeq(seqval) VALUES(42);

SELECT
    *
    ,seqval + 1 AS start_range_seqval_plus_1
    ,case when NOT EXISTS (SELECT * FROM dbo.NumSeq AS B WHERE B.seqval = A.seqval + 1) then 1 else 0 end filt1
    ,case when seqval < (SELECT MAX(seqval) FROM dbo.NumSeq) then 1 else 0 end filt2
FROM 
    dbo.NumSeq AS A
;

SELECT
    seqval + 1 AS start_range
    ,(SELECT MIN(B.seqval) 
      FROM dbo.NumSeq AS B
      WHERE B.seqval > A.seqval) - 1 AS end_range
FROM 
    dbo.NumSeq AS A
WHERE 1=1
    --"The purpose of the NOT EXISTS predicate in the WHERE clause is to filter only points that are a point before a gap. 
    --You can identify a point before a gap whenyou see that for such a point, the value plus 1 doesn’t exist in the sequence."
    AND NOT EXISTS (SELECT * 
                    FROM dbo.NumSeq AS B
                    WHERE B.seqval = A.seqval + 1)
    AND seqval < (SELECT MAX(seqval) FROM dbo.NumSeq)
;


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development