CODEWITHSUNDEEP
sqldatediffcross-applyouter-apply
learning coding
learning coding

Reputation: 3

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?

Upvotes: 0

Views: 42

Answers (2)

Stu
Stu

Reputation: 32599

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

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

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

Upvotes: 0

Related Questions