sqldatediffcross-applyouter-apply

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


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?


Solution

  • 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