1

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.

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.

  • Hi thanks for your help! Will need to come up with an alternative method... this kills performance. It's gone from running in one min., i stopped the query after 5 mins... Can I pivot the duplicate location ID and base on status receipt order? Or create another locationid and use OVER somehow or subquery or something? Or if it's best can this be easily done in SSRS? This will end up in SSRS... – jhowe Oct 30 '14 at 14:41
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.