Return first matching row from one side of a join?

Transact-SQL
1

Afternoon all,

I have 2 tables I'm working with here, 'Employees' contains all the employee details & 'Notes' contains a list of dates and notes.

I'm trying to get a list of all employees thats havent had any notes in the last 3 weeks with the latest date & latest note, the query below essentially works but I'm sure there is a better way. Could someone give me some advice as to the best way to approach this problem please.

Thanks

SELECT Emp_Name, MAX( CAST(Notes_DateTime AS DATE)) As Notes_DateTime, Emp_Status, Emp_Area
FROM Employees
INNER JOIN Notes ON Emp_ID = Notes_EmpID
WHERE Notes_DateTime < dateadd(WEEK,-3,GETDATE())
GROUP BY Emp_Name, Emp_Status, Emp_Area
2 
create table #persons(Emp_ID int, Emp_Name varchar(50), 
lastname varchar(50), Emp_Status varchar(50), 
Emp_Area varchar(50) default('Star Wars'))

create table #personnotes(Notes_EmpID int, note varchar(500), 
Notes_DateTime  datetime)


insert into #persons
select 1, 'Darth', 'Vader', 'Dead', 'Star Wars' union
select 2, 'Luke', 'Skywalker', 'Dead', 'Star Wars' union
select 3, 'Chewy', 'Backer', 'Grrrrrraaa', 'Star Wars' union
select 4, 'Princess', 'Lee Yah', 'Dead', 'Star Wars' union
select 5, 'Ewok dem little teddy bears', 'Real Woke', 'Cute', 'Star Wars' union
select 6, 'Darth', 'Revan', 'Dead', 'Star Wars' union
select 7, 'Darth', 'Sidious', 'Dead', 'Star Wars'

insert into #personnotes
select Emp_ID, t.name, t.create_date From #persons
cross apply msdb.sys.tables t

SELECT Emp_Name, MAX( CAST(Notes_DateTime AS DATE)) As Notes_DateTime, 
Emp_Status, Emp_Area
FROM #persons
INNER JOIN #personnotes ON Emp_ID = Notes_EmpID
WHERE Notes_DateTime < dateadd(WEEK,-3,GETDATE())
GROUP BY Emp_Name, Emp_Status, Emp_Area

SELECT top 1 Emp_Name, CAST(Notes_DateTime AS DATE) As Notes_DateTime, 
Emp_Status, Emp_Area
FROM #persons
INNER JOIN #personnotes ON Emp_ID = Notes_EmpID
WHERE Notes_DateTime < dateadd(WEEK,-3,GETDATE())
order by CAST(Notes_DateTime AS DATE) desc

drop table #persons
drop table #personnotes
3 
SELECT E.Emp_Name, CAST(N.Notes_DateTime AS DATE) As Notes_DateTime, E.Emp_Status, E.Emp_Area
FROM Employees E
CROSS APPLY (
    SELECT TOP (1) N2.*
    FROM Notes N2
    WHERE N2.Emp_ID = E.Emp_ID AND N2.Notes_DateTime < DATEADD(WEEK,-3,CAST(GETDATE() AS date))
    ORDER BY N2.Notes_DateTime DESC
) AS N
ORDER BY E.Emp_Name
4

Perfect, thank you!