Sequential group in SQL

Edit: This problem was too interesting to forget about. It took me awhile, but I combined dportas's solution with my function. The result is at the bottom of this answer.

Okay. I converted (a limited version of) the stored procedure to a function. If the values are delimited in a single column, use this to convert to a table and then use dportas's solution.

-- Usage:   	SELECT * FROM dbo.ConvertListToTable('1,2,3,4,5,6', ',')
-- =============================================
CREATE FUNCTION ConvertListToTable 
(
    @List varchar(max),
    @Delimiter varchar(5)
)
RETURNS 
@ListTable TABLE 
(
    value int -- use whatever data type makes sense for proper ordering
)
AS
BEGIN
    DECLARE @ValueStartIndex int, @ValueEndIndex int

    SET @ValueStartIndex = 1

    WHILE @ValueStartIndex <= LEN(@List)
    BEGIN
    	SET @ValueEndIndex = CHARINDEX(@Delimiter, @List, @ValueStartIndex)
    	IF @ValueEndIndex = 0
    		SET @ValueEndIndex = LEN(@List) + 1 -- get remainder of list

    	INSERT INTO @ListTable (value)
    	VALUES (SUBSTRING(@List, @ValueStartIndex, @ValueEndIndex - @ValueStartIndex))

    	SET @ValueStartIndex = @ValueEndIndex + 1
    END
    RETURN 
END
GO

Here's the sample data I used:

-- This creates a temporary Numbers table, but you might already have a permanent one. Just make sure the list goes high enough.
DECLARE @Numbers TABLE (num int)
INSERT INTO @Numbers
SELECT value
FROM dbo.ConvertListToTable('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20',',')

-- This would be your data table.
DECLARE @tbl TABLE (id int, list varchar(max))
INSERT INTO @tbl (id, list)
SELECT 1, '1,2,3,4,6,7,9,12'
UNION
SELECT 2, '1,3,5'
UNION
SELECT 3, '1,2,4,5,7,9'

Once you have the function, here's how you call it. You'll need to change @tbl and @Numbers to work for your database.

;WITH t AS
(
    SELECT id, num, num - ROW_NUMBER() OVER (ORDER BY id, num) r
    FROM
    (
    	SELECT t.id, n.num
    	FROM @tbl t, @Numbers n
    	WHERE EXISTS
    		(
    			SELECT LT.value
    			FROM dbo.ConvertListToTable(t.list, ',') LT
    			WHERE LT.value = n.num
    		)
    ) t1
)
SELECT id, MIN(num) mn, MAX(num) mx
FROM t
GROUP BY id, r
ORDER BY id, mn, mx

Here's the result:

id  mn	mx
1   1	4
1   6	7
1   9	9
1   12	12
2   1	1
2   3	3
2   5	5
3   1	2
3   4	5
3   7	7
3   9	9

Assuming you have these numbers as rows in you table then try:

WITH t AS (
  SELECT num, num - ROW_NUMBER() OVER (ORDER BY num) r
  FROM tbl t1)
SELECT MIN(num) mn, MAX(num) mx
FROM t
GROUP BY r;

This is like the question at Stack Overflow.

Seems my answer is now used on their site, and presumably this one too!

http://stackoverflow.com/questions/1176011/sql-to-determine-minimum-sequential-days-of-access/1176255#1176255