Sql-server – Pivot column query

pivotsql server

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.

Best Answer

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.

Related Question