SELECT COUNT(CID), startdate, enddate
FROM
TABLE1 a
INNER JOIN TABLE2 b ON
a.date >= b.startdate
AND a.date <= b.enddate
GROUP BY
b.startdate,
b.enddate;
If there are overlapping dates in TABLE2, the total count can be more than the number of CID's in TABLE1.
If your dates have a time portion to it, you need modify the query a little bit to make sure that you pick up CID's that fall on enddates.