Take the last in the query

Question

I have a query like this:

Select *
From Table1 as ad
inner join Table2 as u
on u.employee_ident=ad.employee_ident
inner join Table3 as t
On u.employee_ident=t.employee_ident and u.hire_date=t.hire_date
where DATEDIFF(day,t.term_date,GETDATE() )>=60  AND u.status in ('nohire','1') and u.company_group_abbr_name='ABC' 
order by
 t.term_date asc

Table3 for the same user has more than one term_date. I want that when I run this query in the moment that the compare will be done in DATEDIFF(day,t.term_date,GETDATE() )>=60 in the part of t.term_date it will take the last one. Actually when I run it it makes the compare with the first one that it finds.
So from the dates 2018, 2020, and 2022 it compares with 2018 and I want it to make the compare with 2022 which is the most recent one. How can I do this?

Answer 1

Try something like this:

WITH T3Latest (
    employee_ident,
    hire_date,
    term_date,
    term_rank
    )
AS (
    SELECT employee_ident,
        hire_date,
        term_date,
        RANK() OVER (
            PARTITION BY employee_ident ORDER BY term_date DESC
            ) term_rank
    FROM Table3
    )
SELECT *
FROM Table1 AS ad
INNER JOIN Table2 AS u ON u.employee_ident = ad.employee_ident
INNER JOIN T3Latest AS t ON u.employee_ident = t.employee_ident
    AND u.hire_date = t.hire_date
WHERE t.term_rank = 1
    AND DATEDIFF(day, t.term_date, GETDATE()) >= 60
    AND u.STATUS IN (
        'nohire',
        '1'
        )
    AND u.company_group_abbr_name = 'ABC'
ORDER BY t.term_date ASC;