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?
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