select records which are repeated twice!

Question

I have a table in SQL Server with these records :

ID         Date     Time
--     ---------    ----
1      09/05/02     6:00
2      09/05/02     8:00
3      09/05/03     6:00
4      09/05/04     8:00
5      09/05/04     6:00

I would like to select those ID who have records in a day which are repeated twice or multiple of two.

How would I do this in SQL Server?

Answer 1

Something like:

select table_1.id, table_1.date from
table as table_1 inner join table as table_2 on 
table_1.date = table_2.date 
and 
table_1.id <> table_2.id

should work alright.

Answer 2

this query just select records with ID=1 and days which are repeated twice or multiple of 2 :

SELECT  *
FROM     MyTable
WHERE  (Date IN
          (SELECT  Date
           FROM  MyTable
           WHERE  (ID = 1)
           GROUP BY Date
          HAVING  (COUNT(Date) % 2 = 0)
          )
       )
 AND (ID = 1)