Get the latest entry time using SQL if your result returns two different time, should I use cross or outer apply?

Question

So I want to use datediff for two tables that I'm doing a join on. The problem is if I filter by a unique value, it returns two rows of result. For example:

select *
from [internalaudit]..ReprocessTracker with (nolock)
where packageID = '1983446'

It returns two rows, because it was repackaged twice, by two different workers.

User               RepackageTime

KimVilder          2021-06-10
DanielaS           2021-06-05

I want to use the latest repackagetime of that unique packageID and then do a datediff with another time record when I do a join with a different table.

Is there way to filer so I can get the latest time entry of Repackagetime?

Answer 1

There are numerous ways you can accomplish this, if I understand your goal - proper example data and tables would be a help here.

One way is using apply and selecting the max date for each packageId

select DateDiff(datepart, t.datecolumn, r.RepackageTime)...
from othertable t
cross apply (
    select Max(RepackageTime)RepackageTime
    from internalaudit.dbo.ReprocessTracker r
    where r.packageId=t.packageId
)r

Answer 2

select * 
from Othertable t1
join (
select *
from [internalaudit]..ReprocessTracker t2
where packageID = '1983446'
limit 1
) t2
on t1.id = t2.id 

if you are using sql server instead of limit 1 you should use top 1 also otherwise you solid reason to use nolock hint, avoid using it.

also to generalize the query above:

select * 
from Othertable t1
cross join (
select *
from [internalaudit]..ReprocessTracker t2
where t1.packageID = t2.packageID
limit 1
) t2