Pivot column query

Question

I'm trying to figure out how to create a From and To column based on ID.LocationID column.

I'm hoping with a suggestion this can be done (I can figure it out) without having to create sample tables and data as that would take me all day. (fairly complicated query) I'll paste basic structure to give you an idea of what i'm doing:

;WITH    ITR
          AS ( SELECT   CREATEDDATETIME ,
                        CREATEDBY ,
                        INVENTID ,
                        ITO.REFERENCECATEGORY ,
                        IT.STATUSRECEIPT
               FROM     dbo.TRANS IT
                        INNER JOIN TRANSORIGIN ITO ON IT.TRANSORIGIN = ITO.RECID
                                                            AND ITO.REFCATEGORY IN (
                                                            2, 6 )
               WHERE    CREATEDDATETIME > DATEADD(MONTH, -1, GETDATE())
             )
    SELECT  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    dbo.PALLETTABLE AS YPT
            INNER JOIN dbo.PALLET AS WP ON YPT.PALLETID = WP.PALLETID
            LEFT OUTER JOIN dbo.DIM AS ID ON WP.PALLETID = ID.PALLETID
                                                   AND ID.BATCHID <> ''
            LEFT OUTER JOIN dbo.ORDER AS WOT ON ID.DIMID = WOT.DIMID
            LEFT OUTER JOIN ITR ON ID.DIMID = ITR.DIMID
            ORDER BY WP.PALLETNUMBER

Basically I'm selecting from huge table (in a CTE) then selecting from this and joining on to other tables below. Sample output:

CREATEDDATETIME        User PalletNo    Current Location    Pallet  Depot   REFERENCECATEGORY   STATUSRECEIPT
2014-10-10 08:50:10.000 A  C2002    CRK2    1285952 NULL    6   0
2014-10-10 08:50:10.000 A  C2002    300FR   1285952 NULL    6   1
2014-10-10 09:07:16.000 B  C2002    300FR   1285952 NULL    6   0
2014-10-10 09:07:16.000 B  C2002    23IS1   1285952 NULL    6   1

As you can see, I have 4 rows and 2 sets. Where the CREATEDDATETIME is the same I want to combine the rows and add a 'Previous Location' column based on ascending status receipt order. (Status receipt identifies previous row)

How can this be done? This will end up in SSRS.

Answer 1

The following will work with some assumptions:

1. statusreciept will be 0 or 1- if other values are possible and you want EACH prior location concatenated in the "Prior location" field, that is also doable, but requires a more robust solution.

2. that createddatetime is unique for all sets you want to track

        ;WITH    ITR
                     AS ( SELECT   CREATEDDATETIME ,
                                   CREATEDBY ,
                                   INVENTID ,
                                   ITO.REFERENCECATEGORY ,
                                   IT.STATUSRECEIPT
                          FROM     dbo.TRANS IT
                                   INNER JOIN TRANSORIGIN ITO ON IT.TRANSORIGIN = ITO.RECID
                                                                       AND ITO.REFCATEGORY IN (
                                                                       2, 6 )
                          WHERE    CREATEDDATETIME > DATEADD(MONTH, -1, GETDATE())
                        ),
                 DS AS
       (
               SELECT  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    dbo.PALLETTABLE AS YPT
                       INNER JOIN dbo.PALLET AS WP ON YPT.PALLETID = WP.PALLETID
                       LEFT OUTER JOIN dbo.DIM AS ID ON WP.PALLETID = ID.PALLETID
                                                              AND ID.BATCHID <> ''
                       LEFT OUTER JOIN dbo.ORDER AS WOT ON ID.DIMID = WOT.DIMID
                       LEFT OUTER JOIN ITR ON ID.DIMID = ITR.DIMID
                       ORDER BY WP.PALLETNUMBER),
      msr AS
   (   SELECT createddatetime, max(statusreciept) statusreciept
   FROM ds
   GROUP BY createddatetime)
   
   
   
   SELECT ds.createddatetime,
          ds.user,
          ds.palletnumber,
          ds.[current location],
          ds2.[current location] as [Previous Location],
          ds.pallet,
          ds.depot,
          ds.reference_category,
          ds.statusreciept AS statusreciept
   FROM ds
   LEFT JOIN ds ds2 
      ON ds2.createddatetime = ds.createddatetime
      AND ds2.statusreciept = ds.statusreciept - 1
   INNER JOIN msr 
      ON msr.createddatetime = ds.createddatetime
      AND msr.statusreciept = ds.statusreciept
   GROUP BY ds.createddatetime,
          ds.user,
          ds.palletnumber,
          ds.[current location],
          ds2.[current location],
          ds.pallet,
          ds.depot,
          ds.reference_category
   

Please let me know if either of the assumptions are incorrect or you need a different implementation.

Answer 2

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;