mickyT (9/22/2013)

---

Hi

I think this covers what you want to do. I suspect the performance will be poor.

WITH groupit AS (

-- Group up the Depts on data

SELECT CID, CDate, Dept,

ROW_NUMBER() OVER (PARTITION BY CID ORDER BY CDate ASC) - ROW_NUMBER() OVER (PARTITION BY CID, Dept ORDER BY CDate ASC) GroupNum

FROM #tTable t

),

getDeptGC AS (

-- Do counts in the groups for dept C

SELECT CID, CDate, Dept, GroupNum,

COUNT(*) OVER (PARTITION BY CID, Dept, GroupNum) C

FROM groupit a

WHERE Dept = 'C'

),

getDeptR AS (

-- Number the rows in groups of more than 1

SELECT CID, CDate, Dept, GroupNum,

ROW_NUMBER() OVER (PARTITION BY CID, Dept ORDER BY CDate ASC) N

FROM getDeptGC

WHERE C > 1

)

SELECT CID, CDate, Dept FROM getDeptR WHERE N = 2

ORDER BY CID;