--
-- DDL
--
if  exists (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TMP_TASKS]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.TMP_TASKS
go
 CREATE TABLE dbo.TMP_TASKS (
    SEQ_NO    int IDENTITY NOT NULL,
    INDEX_NO  int NOT NULL,
)
--
-- Sample data
--
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (2)
INSERT INTO TMP_TASKS VALUES (2)
INSERT INTO TMP_TASKS VALUES (2)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (2)
INSERT INTO TMP_TASKS VALUES (3)
INSERT INTO TMP_TASKS VALUES (3)
INSERT INTO TMP_TASKS VALUES (3)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (2)
INSERT INTO TMP_TASKS VALUES (2)
INSERT INTO TMP_TASKS VALUES (3)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (0)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (1)
INSERT INTO TMP_TASKS VALUES (2)
INSERT INTO TMP_TASKS VALUES (2)
--
-- View (coz CTE is used to write reusable and complex queries
-- but unfortunatly a a recursive CTE has lot of restrictions such as, 
-- cant have outer join and cte cannot be in subquery which is very much needed for this solution)
--
if exists (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[VW_TMP_TASKS]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW dbo.VW_TMP_TASKS
go
CREATE VIEW dbo.VW_TMP_TASKS AS
    SELECT A.SEQ_NO,A.INDEX_NO CUR_IDX_NO,ISNULL(B.INDEX_NO,-1) NXT_IDX_NO
      FROM TMP_TASKS A
           LEFT OUTER JOIN TMP_TASKS B
        ON A.SEQ_NO = B.SEQ_NO + 1
GO
    --
    -- Actual query (recursive CTE)
    --
      WITH CTE_TASKS (SEQ_NO,CUR_IDX_NO,NXT_IDX_NO,CYCLE_ID) AS (
    -- Anchor member (god i wasted lot of time by jumping into writing recursive query without understanding it throughly)
    SELECT SEQ_NO,CUR_IDX_NO,NXT_IDX_NO,1 CYCLE_ID
      FROM VW_TMP_TASKS
     WHERE SEQ_NO = (SELECT MIN(SEQ_NO)
                     FROM VW_TMP_TASKS)
 UNION ALL
    -- Recursive member
    SELECT A.SEQ_NO,A.CUR_IDX_NO,A.NXT_IDX_NO,
           CASE WHEN A.CUR_IDX_NO=A.NXT_IDX_NO THEN B.CYCLE_ID ELSE B.CYCLE_ID+1 END
      FROM VW_TMP_TASKS A
           INNER JOIN CTE_TASKS B
        ON A.SEQ_NO = B.SEQ_NO+1
)
--

  -- Now select

  SELECT SEQ_NO,CUR_IDX_NO,CYCLE_ID FROM CTE_TASKS

  -- Select first and last sequence number from each task cycle

  SELECT CYCLE_ID,MIN(SEQ_NO)FIRST_SEQ_NO,MAX(SEQ_NO)LAST_SEQ_NO
    FROM CTE_TASKS
GROUP BY CYCLE_ID