--Create transaction table
CREATE TABLE #table1 (ID INT IDENTITY
	,PhoneNumber VARCHAR(12)
	,CreateDate DATETIME)
--Create a call table
CREATE TABLE #table2 (LVTransactionID INT
	,CallWindowStart DATETIME
	,CallWindowEnd DATETIME
	,CallNumber INT)

--Populate transactions with 8 unique phone numbers
INSERT INTO #table1
SELECT '310 404 1001', '20090227 01:00:00'
UNION ALL
SELECT '310 404 1002', '20090227 02:00:00'
UNION ALL
SELECT '310 404 1003', '20090227 03:00:00'
UNION ALL
SELECT '310 404 1004', '20090227 04:00:00'
UNION ALL
SELECT '310 404 1005', '20090227 05:00:00'
UNION ALL
SELECT '310 404 1006', '20090227 06:00:00'
UNION ALL
SELECT '310 404 1007', '20090227 07:00:00'
UNION ALL
SELECT '310 404 1008', '20090227 08:00:00'

--For performance testing assume that these phone numbers were used in a large number of transactions each
INSERT INTO #table1
SELECT a.PhoneNumber, a.CreateDate FROM #table1 a, #table1 b, #table1 c, #table1 d

--Populate table2 with one call for each transaction
INSERT INTO #table2
SELECT ID, CreateDate, DATEADD(mi,1,CreateDate), 1
FROM #table1
--A minute later a new call is placed for all but the first transaction
INSERT INTO #table2
SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1
FROM #table2
WHERE LVTransactionID > 1 AND CallNumber = 1
--A minute later a new call is placed for all but the two first transactions
INSERT INTO #table2
SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1
FROM #table2
WHERE LVTransactionID > 2 AND CallNumber = 2
--A minute later a new call is placed for all but the three first transactions
INSERT INTO #table2
SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1
FROM #table2
WHERE LVTransactionID > 3 AND CallNumber = 3
--A minute later a new call is placed for all but the four first transactions
INSERT INTO #table2
SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1
FROM #table2
WHERE LVTransactionID > 4 AND CallNumber = 4

--We now have data for testing the result and performance of different methods
SELECT 'Testing performance: '+CAST((SELECT COUNT(*) FROM #table1) AS varchar(10))+' transactions and '+CAST((SELECT COUNT(*) FROM #table2) AS varchar(10))+' calls'

DECLARE @StartTime DATETIME

--Test CTE/ROW_NUMBER method
SET @StartTime = GETDATE();
WITH lvc AS 
(
    SELECT LVTransactionID
		, CallWindowStart
		, CallWindowEnd
		, CallNumber
		, ROW_Num = ROW_NUMBER() OVER
	(PARTITION BY LVTransactionID 
	ORDER BY CallWindowStart DESC)
    FROM #table2
)
SELECT lvt.ID
	,lvt.PhoneNumber
	, lvt.CreateDate
	, lvc.CallWindowStart
	, lvc.CallWindowEnd
	, lvc.CallNumber
FROM #table1 lvt
INNER JOIN lvc ON lvt.ID=lvc.LVTransactionID
WHERE ROW_Num <= 3

SELECT DATEDIFF(millisecond, @StartTime, GETDATE()) AS [ExecutionTime CTE/ROW_NUMBER]

--Test the OUTER APPLY method
SET @StartTime = GETDATE();
SELECT
 lvt.ID,
 lvt.PhoneNumber,
 lvt.CreateDate,
 lvc.CallWindowStart,
 lvc.CallWindowEnd,
 lvc.CallNumber
FROM
 #table1 lvt
OUTER APPLY 
(
 SELECT top 3 * 
 FROM #table2 
 WHERE LVTransactionID = lvt.ID 
 ORDER BY CallWindowStart DESC
 ) AS lvc
SELECT DATEDIFF(millisecond, @StartTime, GETDATE()) AS [ExecutionTime OUTER APPLY]

DROP TABLE #table1
DROP TABLE #table2
GO