This is a rewrite with ROWNUMBER() & LEAD(). Performance will need to be checked, but it might still be too slow. Also added some temp tables that mimic part of table structure shown in the query (some differences). CREATE TABLE #TRANS(STATUSRECEIPT int, TRANSORIGIN int, DIMID int); INSERT INTO #TRANS values(0,1,1), (1,1,2),(0,2,3),(1,3,4) CREATE TABLE #TRANSORIGIN ( RECID int identity(1,1) PRIMARY KEY NOT NULL, CREATEDDATETIME datetime2 default getdate() , createdby nvarchar(255), REFERENCECATEGORY int); INSERT INTO #TRANSORIGIN(createdby,REFERENCECATEGORY) values('A',2),('A',6); WAITFOR DELAY '00:00:02' INSERT INTO #TRANSORIGIN(createdby,REFERENCECATEGORY) values('B',2),('B',6),('C',6); --ALTER TABLE #trans add constraint FK_TRANSORIGIN_TRANS foreign key (TRANSORIGIN) references #TRANSORIGIN(recid); not possible on temp tables CREATE TABLE #PALLETTABLE( PALLETID int, PALLETNUMBER varchar(10)); INSERT INTO #PALLETTABLE VALUES(1285952,'C2002'),(2,'C2002'),(3,'C2002'),(4,'C2002'); CREATE TABLE #PALLET(PALLETID int, PALLETNUMBER varchar(10)); INSERT INTO #PALLET VALUES(1285952,'C2002 '); --,(1285952,2),(3,3),(4,4) CREATE TABLE #DIM(DIMID int identity (1,1), PALLETID int, BATCHID int, LOCATIONID varchar(10)); INSERT INTO #DIM(PALLETID,BATCHID,LOCATIONID) VALUES(1285952,1,'CRK2'), (1285952 ,2,'300FR'), (1285952 ,3,'300FR'), (1285952 ,4,'23IS1'); CREATE TABLE #ORDER(Orderid int identity(1,1), DIMID int, DEPOTNAME nvarchar(255)); INSERT INTO #ORDER(DIMID,DEPOTNAME) VALUES(1,'DEPOT1'),(2,'DEPOT2'),(3,'DEPOT4'),(4,'DEPOT5'); ;WITH ITR AS ( SELECT CREATEDDATETIME , CREATEDBY , ITO.REFERENCECATEGORY , IT.STATUSRECEIPT, IT.DIMID FROM #TRANS IT INNER JOIN #TRANSORIGIN ITO ON IT.TRANSORIGIN = ITO.RECID AND ITO.REFERENCECATEGORY IN ( 2, 6 ) WHERE CREATEDDATETIME > DATEADD(MONTH, -1, GETDATE()) ), ITR2 as( SELECT LEAD(LOCATIONID) OVER ( PARTITION BY CREATEDDATETIME ORDER BY STATUSRECEIPT asc) as nextvalue, ROW_NUMBER() OVER ( PARTITION BY CREATEDDATETIME ORDER BY STATUSRECEIPT asc) as rownum, ITR.CREATEDDATETIME , ITR.CREATEDBY AS [User] , YPT.PALLETNUMBER AS PalletNumber , ID.LOCATIONID AS [Current Location] , ID.PALLETID AS Pallet , WOT.DEPOTNAME AS Depot , ITR.REFERENCECATEGORY , ITR.STATUSRECEIPT FROM #PALLETTABLE AS YPT INNER JOIN #PALLET AS WP ON YPT.PALLETID = WP.PALLETID LEFT OUTER JOIN #DIM AS ID ON WP.PALLETID = ID.PALLETID AND ID.BATCHID <> '' LEFT OUTER JOIN #ORDER AS WOT ON ID.DIMID = WOT.DIMID LEFT OUTER JOIN ITR ON ID.DIMID = ITR.DIMID) select ITR2.CREATEDDATETIME , ITR2.[User] , ITR2.PalletNumber , ITR2.[Current Location] , nextvalue, ITR2.Pallet , ITR2.Depot , ITR2.REFERENCECATEGORY , ITR2.STATUSRECEIPT FROM ITR2 where ITR2.rownum = 1; DROP TABLE #TRANS; DROP TABLE #TRANSORIGIN; DROP TABLE #PALLETTABLE; DROP TABLE #PALLET; DROP TABLE #DIM; DROP TABLE #ORDER;