-1

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

NihilisticWonder
  • 3
  • 3
  • Which version of sql server (please tag your questions appropriately)? Since v2012 [we have `LEAD()` and `LAG()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017) – Shnugo Jul 02 '18 at 10:33
  • We are using 2008R2 – NihilisticWonder Jul 02 '18 at 11:00
  • The `APPLY` will compute data *row-wise* while a `JOIN` will try to connect two sets over a given criterion. In your case - as you want to get the *previous* entry the *row-wise* approach looks pretty much okay... – Shnugo Jul 02 '18 at 11:10
  • Yes it is funcional, but the query is taking far too long to execute, and I need to cut the execution time down as much as I can. – NihilisticWonder Jul 02 '18 at 12:22
  • The approach of the previous question would need indexes on EntryTime and PhoneNumber. Do you have the needed indexes in your database? Are there other processes involved? – Shnugo Jul 02 '18 at 16:42

1 Answers1

0

You might use the following approach:

  • Use a CTE to create a set and use ROW_NUMBER to number your calls.
  • ROW_NUMBER allows to specify the OVER() clause with a PARTITION BY and an ORDER BY part

This will return a list of your phone calls, where each phone number as a consecutive index in the order of 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;
Shnugo
  • 66,100
  • 9
  • 53
  • 114