dwain.c (2/5/2014)

---

I agree that your expected results don't match your sample data. But I think you're looking for something like this:

SELECT id, cid, startdate=MIN(d)

, enddate=CASE

WHEN DATEPART(year, MIN(d)) = DATEPART(year, GETDATE()) THEN NULL

WHEN DATEPART(year, MAX(d)) = DATEPART(year, MIN(d)) AND MAX(d) <> MIN(d) THEN MAX(d)

ELSE DATEADD(year, 1, DATEADD(year, DATEDIFF(year, 0, MIN(d)), 0))-1 END

FROM

(

SELECT id, cid, startdate, d

,rn=ROW_NUMBER() OVER (PARTITION BY id, cid ORDER BY d)/2

FROM #table

CROSS APPLY

(

VALUES (startdate),(startdate-1)

) b (d)

) a

WHERE rn > 0

GROUP BY id, cid, rn;

Output from your sample data:

id cid startdate enddate

1 100 2013-06-04 00:00:00.000 2013-06-16 00:00:00.000

1 100 2013-06-17 00:00:00.000 2013-06-24 00:00:00.000

1 100 2013-06-25 00:00:00.000 2013-12-31 00:00:00.000

1 100 2014-02-23 00:00:00.000 NULL

2 200 2013-08-03 00:00:00.000 2013-08-08 00:00:00.000

2 200 2013-08-09 00:00:00.000 2013-12-31 00:00:00.000