ProductEmployee.sql

use TestDB

if object_id('tempdb..#ProductEmployee') is not null drop table #ProductEmployee
if object_id('tempdb..#Employee') is not null drop table #Employee
if object_id('tempdb..#Product') is not null drop table #Product

-- No PK/FK definitions for brevity
create table #Employee
(
    ID int,
    Name nvarchar(128),
    Age int
)

create table #Product
(
    ID int,
    Name nvarchar(128)
)

create table #ProductEmployee
(
    EmployeeId int,
    ProductId int,
    Date datetime
)

insert into #Employee
values
(1, 'Name1', 50),
(2, 'Name2', 50),
(3, 'Name3', 49)

insert into #Product
values
(1, 'Prod1'),
(2, 'Prod2'),
(3, 'Prod3'),
(4, 'Prod4'),
(5, 'Prod5'),
(6, 'Prod6'),
(7, 'Prod7'),
(8, 'Prod8'),
(9, 'Prod9'),
(10, 'Prod10')

insert into #ProductEmployee
values
(1, 1, '2015-01-01'),
(1, 2, '2010-01-01'),
(1, 3, '2010-01-01'),
(1, 4, '2010-01-01'),
(1, 5, '2010-01-01'),
(1, 6, '2016-01-01'),
(1, 7, '2010-01-01'),
(1, 8, '2010-01-01'),
(1, 9, '2010-01-01'),
(1, 10, '2010-01-01'),
(2, 1, '2010-01-01'),
(2, 1, '2010-01-01'),
(2, 1, '2010-02-01'),
(2, 1, '2010-03-01'),
(2, 1, '2010-04-01'),
(2, 1, '2010-05-01'),
(2, 1, '2010-06-01'),
(2, 1, '2010-07-01'),
(2, 1, '2010-08-01'),
(2, 1, '2010-09-01'),
(2, 9, '2010-05-01'),
(3, 1, '2010-01-01'),
(3, 2, '2010-05-01')

-- First solution
select pe.EmployeeAge, pe.EmployeeName, pe.ProductName 
from 
(
    select e.Id from #Employee as e
    join #ProductEmployee as pe on pe.EmployeeId = e.Id
    where e.Age >= 50
    group by e.Id
    having count(distinct(pe.ProductId)) >= 10
) as e
join 
(
    select e.Id as EmployeeId, e.Name as EmployeeName, e.Age as EmployeeAge, p.Name as ProductName 
    from #Employee as e
    join #ProductEmployee as pe on pe.EmployeeId = e.Id
    join
    (
        select pe.EmployeeId, max(pe.Date) as Date from #ProductEmployee as pe
        group by pe.EmployeeId
    ) as tmp on tmp.EmployeeId = e.Id and tmp.Date = pe.Date 
    join #Product as p on p.Id = pe.ProductId
) as pe on pe.EmployeeId = e.Id

--Second solution
select 
    e.Age as EmployeeAge, 
    e.Name as EmployeeName,
    max(case when tmp.Date is null then null else p.Name end) as ProductName 
from #Employee as e
join #ProductEmployee as pe on pe.EmployeeId = e.Id
join #Product as p on p.Id = pe.ProductId
left join
(
    select pe.EmployeeId, max(pe.Date) as Date from #ProductEmployee as pe
    group by pe.EmployeeId
) as tmp on tmp.EmployeeId = e.Id and tmp.Date = pe.Date
where e.Age >= 50
group by e.Id, e.Name, e.Age
having count(distinct(p.Id)) >= 10

--Third solution
select e.Age as EmployeeAge, e.Name as EmployeeName, p.Name as ProductName 
from #Employee as e
join
(
    select e.ID as EmployeeId, max(pe.Date) as LastDate
    from #ProductEmployee as pe
    join #Employee as e on e.ID = pe.EmployeeId
    where e.Age >= 50
    group by e.ID
    having count(distinct(pe.ProductId)) >= 10
) as tmp on tmp.EmployeeId = e.ID
join #ProductEmployee as pe on pe.EmployeeId = e.ID and pe.Date = tmp.LastDate
join #Product as p on p.ID = pe.ProductId