• Sorry! May be I`m not explaining it properly.

    One more CID=333 is added below SQL.

    IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL

    DROP TABLE #tTable

    CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(10))

    INSERT INTO #tTable(CID, CDate, Dept)

    VALUES

    (333, '2012-01-14 00:00:00.000','A'),

    (333,'2012-02-14 00:00:00.000','C'),

    (333,'2012-03-14 00:00:00.000','B'),

    (333,'2012-04-14 00:00:00.000','C'),

    (333,'2012-08-15 00:00:00.000','C'),

    (333,'2012-09-20 00:00:00.000','C'),

    (333,'2012-10-26 00:00:00.000','B'),

    (333, '2013-01-14 00:00:00.000','A'),

    (333,'2013-02-14 00:00:00.000','C'),

    (333,'2013-03-14 00:00:00.000','B'),

    (333,'2013-04-14 00:00:00.000','C'),

    (333,'2013-05-14 00:00:00.000','C')

    SELECT * FROM #tTable

    DROP TABLE #tTable

    Note that the ORDER of the CDate is in ASC order and it should not be changed for that CID.

    GROUP(s) is ONLY for Dept=C:

    If Dept=C is followed by another Dept=C or more Dept=C values it is considered as GROUP 1 for that CID...

    AND for GROUP 2 same logic..if Dept=C is followed by another Dept=C or more Dept=C values it is considered as GROUP 2 for that CID...

    Output needed is the 2nd row of the GROUP 1.