Multi tool use

Rewriting Outer apply to join for performance

Since I am not that familiar with Outer Apply, I am a bit lost with rewriting the one from my previous question to join. It worked well but not for large chunks of data. Any help with this appreciated.

Previous question

LEAD()

LAG()

APPLY

JOIN

1 Answer
1

You might use the following approach:

CTE

ROW_NUMBER

ROW_NUMBER

OVER()

PARTITION BY

ORDER BY

This will return a list of your phone calls, where each phone number as a consecutive index in the order of EntryTime.

EntryTime

You can then use a self-join to find the corresponding previous row.

declare @table table (
PhoneNumber nvarchar(20),
EntryTime datetime
)

insert into @table values ('(321) 546-7842', dateadd(hour,-30,getdate()));
insert into @table values ('(321) 546-7842', dateadd(hour,-3,getdate()));
insert into @table values ('(251) 546-9442', dateadd(hour,-2,getdate()));

WITH cte AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY PhoneNumber ORDER BY EntryTime) AS CallIndex
,t.*
FROM @table t
)
SELECT *
FROM cte t1
LEFT JOIN cte t2 ON t1.PhoneNumber=t2.PhoneNumber AND t1.CallIndex=t2.CallIndex-1
ORDER BY t1.PhoneNumber, t1.EntryTime;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.