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.