David Burrows - Tuesday, January 17, 2017 7:48 AM

Kingston Dhasian - Tuesday, January 17, 2017 3:05 AM

With no DDL and sample data to test, this is what I could come up with.

SELECT O.OrderID, O.CustomerID, O.CustomerVisitID, O.ItemID, O.ItemDesc, O.TransactionDate,
   F.OrderID AS NOrderId, F.CustomerID AS NCustomerID, F.CustomerVisitID AS NCustomerVisitID,
   F.ItemID AS NItemID, F.ItemDesc AS NItemDesc, F.TransactionDate AS NTransactionDate
FROM Orders AS O
CROSS APPLY (
     SELECT TOP 1 *
     FROM Orders AS N
     WHERE N.TransactionDate >='2017-01-01'
     AND  N.ItemID = 2
     AND  DATEDIFF( DAY, O.TransactionDate, N.TransactionDate ) <= 3
     ORDER BY N.TransactionDate DESC ) AS F
WHERE O.TransactionDate >='2017-01-01'
AND  O.ItemID = 1
ORDER BY O.CustomerID, O.CustomerVisitID, O.TransactionDate;

Please post additional details as mentioned in the previous post in case you dont get the desired output using the above query.

Shouldn't that be OUTER APPLY as there may not be any needle orders within range