Select records that are repeated twice!

Question

I have a table in SQL Server with these entries:

  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 IDs that have daily entries that are repeated twice or more of two.

How to do this in SQL Server?

Answer 1

this query simply selects records with ID = 1 and days that are repeated twice or more 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)

Answer 2

Sort of:

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 fine.

Answer 3

this will return the expected result

  declare @aa table (id int,datee date) insert into @aa select 1, '09/05/02' union all select 2, '09/05/02' union all select 3, '09/05/03' union all select 4, '09/05/04' union all select 5, '09/05/04' select * from @aa where datee in ( select datee from @aa group by datee having COUNT(datee)>1) 

Answer 4

Edit

 CREATE TABLE MyTable(ID INTEGER, Date DATETIME) INSERT INTO MyTable VALUES (1, '09/05/02 6:00') INSERT INTO MyTable VALUES (1, '09/05/02 8:00') INSERT INTO MyTable VALUES (2, '09/05/03 6:00') INSERT INTO MyTable VALUES (3, '09/05/04 8:00') INSERT INTO MyTable VALUES (4, '09/05/04 6:00') SELECT t1.* FROM MyTable t1 INNER JOIN ( SELECT t1.ID, Date1 = t1.Date, Date2 = t2.Date FROM MyTable t1 INNER JOIN MyTable t2 ON CAST(t2.Date AS INTEGER) = CAST(t1.Date AS INTEGER) AND DATEPART(HOUR, t2.Date) = DATEPART(HOUR, t1.Date) - 2 AND t2.ID = t1.ID ) t2 ON t2.ID = t1.ID AND t1.Date IN (t2.Date1, t2.Date2) 

Answer 5

It’s not entirely clear to me from your layout whether the date and time are shown in separate columns or only one. That is, you have

 ID theDate theTime -- -------- ------- 1 09/05/02 6:00 1 09/05/02 8:00 2 09/05/03 6:00 3 09/05/04 8:00 4 09/05/04 6:00 

or

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

If the first is true, as it seems, then Ramesh has an almost, but not quite correct answer:

 select * from theTable where theDate in ( select theDate from theTable group by theDate having count(theDate) % 2 = 0 ) 

If the second is true, your work is much more complicated - I will think about it.