SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl
( id int not null,
  date int not null
) ;
insert into tbl
 (id,    date)
values
(1,     2016),
(1,     2017),
(2,     2017),
(2,     2017),
(3,     2016),
(3,     2017),
(4,     2018),
(5,     2018) ;
8 rows affected

-- Mguerra Torres
SELECT id, Date
FROM tbl
WHERE id IN
(select id 
from tbl
group by id
having count(distinct date)>1)


id
Date


1
2016

1
2017

3
2016

3
2017


-- sabin bio
SELECT T.id,T.date
FROM Table1 AS T
CROSS APPLY
  (SELECT C.id 
          ,Count(id) as count_id
          ,Count(Distinct date) as count_Distinct_Records
      FROM Table1 AS C
      WHERE C.id = T.id
      GROUP BY id) AS CA
WHERE
  CA.count_id > 1
  AND count_Distinct_Records > 1
Msg 208 Level 16 State 1 Line 2
Invalid object name 'Table1'.

-- ypercube

-- First, using min(date) < max(date) as the HAVING condition - instead of the count(distinct date) > 1. Perhaps slightly more efficient:
select id, date
from tbl
where id in 
      ( select id
        from tbl
        group by id
        having min(date) < max(date)
      ) ;

-- or with a JOIN:
select t.id, t.date
from tbl as t
     join 
      ( select id
        from tbl
        group by id
        having min(date) < max(date)
      ) as g
      on g.id = t.id ;

-- and one converting the IN to a correlated EXISTS subquery. Bonus, we can remove the GROUP BY:
select id, date
from tbl as t
where exists
      ( select 1
        from tbl as t2
        where t.id = t2.id
          and t.date <> t2.date
      ) ;

-- Another that uses window functions:
select id, date
from
  ( select id, date,
           diff_dates = case when min(date) over (partition by id)
                                < max(date) over (partition by id)
                             then 1
                        end 
    from tbl
  ) as d
where diff_dates = 1 ;

--and last, one for the obfuscation contest:
select id, date from tbl
except
select id, min(date) from tbl group by id
intersect
select id, max(date) from tbl group by id ;


id
date


1
2016

1
2017

3
2016

3
2017




id
date


1
2016

1
2017

3
2016

3
2017




id
date


1
2016

1
2017

3
2016

3
2017




id
date


1
2016

1
2017

3
2016

3
2017




id
date


1
2016

1
2017

3
2016

3
2017