Return to Revisions

This is a rewrite with over(). The performance will probably still be bad. Also some temp tables that mimic part of table structure shown in the query (some differences). EDIT: added LEAD() as to not use a left join.

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;