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.