Identify groups of continuous numbers in a list in SQL Server

Question

Sample data

Column1
-------
   1
   4
  10
  11
  12
  18
  25
  27
  28
  29
  33
  36

Expected output

Continuous Numbers
------------------
     10
     11
     12
     27
     28
     29

count(coutinous numbers)
------------------------
       2 

Answer 1

You need to use Lead And Lag Functions : see this link : SQL Server 2012 Functions - Lead and Lag

-- Declare Table to test : 
       DECLARE @Temp TABLE (Number INT) 
        INSERT INTO @Temp VALUES ( 1 ) , ( 4 ) , ( 10 ) ,( 11 ) ,( 12 ) ,
                                     ( 18 ) ,( 25 ) ,( 27 ) ,( 28 ) ,( 29 ) ,
                                     ( 33 ) ,( 36 );

---------------------------------------------------------------------------------
-- To return the count 

SELECT COUNT(1) CoutinousNumberCount
FROM   (   SELECT Number ,
                  LAG(Temp.Number, 1) OVER ( ORDER BY Temp.Number ) AS PreviousNumber ,
                  LEAD(Temp.Number, 1) OVER ( ORDER BY Temp.Number ) AS NextNumber ,
                  CASE WHEN LAG(Temp.Number, 1) 
                            OVER ( ORDER BY Temp.Number )  = Number - 1 THEN
                           0
                       ELSE 1
                  END AS Separator
           FROM   @Temp AS Temp 
       ) AS result
WHERE  (result.PreviousNumber = result.Number - 1
       OR result.NextNumber = result.Number + 1)
       AND Separator = 1;

-----------------------------------------------------------------------------------------
-- And to return the list:
SELECT Number
FROM   (   SELECT Number ,
                  LAG(Temp.Number, 1) OVER ( ORDER BY Temp.Number ) AS PreviousNumber ,
                  LEAD(Temp.Number, 1) OVER ( ORDER BY Temp.Number ) AS NextNumber 
           FROM   @Temp AS Temp
       ) AS result
WHERE  result.PreviousNumber = result.Number - 1
       OR result.NextNumber = result.Number + 1;