Table join to match without duplicates

I have a situation where I need to match arrivals and departures of assets in a location. The problem is that asset movements are not always entered into the database chronologically, nor is there any current way of tying the arrival and departure together in the database.

Caveats:

• Each movement (arrival or departure) consists of a single row with a unique id (moveID)

• Each row will have an item ID (itemID) that is unique to that item. Each item, however, could have multiple rows (movements) in the table.

• I want to match each arrival for the asset to the departure chronologically, ensuring that we only match each moveID to one or fewer other moves (e.g. we need to allow for an arrival and depature, an arrival only or a departure only).

• Matches should be made based on location (e.g. location and shelf or location and subshelf on arrival matches the location and shelf or location and subshelf on the departure).

• This is a MS SQL Server

The structure of the table is (similar) to the following:

CREATE TABLE Movements
 (
      MoveID int IDENTITY (1,1),
      ItemID int,
      EventType CHAR,
      moveTime datetime,
      LocID int,
      ShelfID int,
      altShelfID int
)

and I have included test data:

```
SET IDENTITY_INSERT movements on

INSERT INTO movements
(MoveID,ItemID,EventType,moveTime,LocID,ShelfID,altShelfID)
VALUES(1,3,'A','2013-01-05 09:00',1,3,NULL)

INSERT INTO movements
(MoveID,ItemID,EventType,moveTime,LocID,ShelfID,altShelfID)
VALUES(2,3,'D','2013-01-06 13:00',1,3,NULL)

INSERT INTO movements
(MoveID,ItemID,EventType,moveTime,LocID,ShelfID,altShelfID)
VALUES(3,3,'A','2013-01-07 09:00',1,3,NULL)

INSERT INTO movements
(MoveID,ItemID,EventType,moveTime,LocID,ShelfID,altShelfID)
VALUES(4,3,'A','2013-01-15 09:00',1,3,NULL)

INSERT INTO movements
(MoveID,ItemID,EventType,moveTime,LocID,ShelfID,altShelfID)
VALUES(5,3,'D','2013-01-07 15:00',1,3,NULL)

INSERT INTO movements
(MoveID,ItemID,EventType,moveTime,LocID,ShelfID,altShel

I think you could use a Row_Number to get the one you want chronologically and then just select that rownum, like this:

;With CTE (ID,Arrival_Time,Departure_Time,ArrivalLocID,ArrivalShelfID,ArrivalAltShelfID,DepartureLocID,DepartureShelfID,DepartureAltShelfID
    ,ArrivalMoveID,DepartureMoveID,RowNum)
AS
(SELECT CASE WHEN sch_A.itemID IS NULL THEN Sch_D.itemID ELSE Sch_A.itemID END AS ID ,
                Sch_A.moveTime AS Arrival_Time ,
                Sch_D.moveTime AS Departure_Time ,
                Sch_A.LocID ,
                Sch_A.ShelfID ,
                Sch_A.AltShelfID ,
                Sch_D.LocID ,
                Sch_D.ShelfID ,
                Sch_D.AltShelfID ,
                Sch_A.MoveID AS ArrivalMoveID ,
                Sch_D.MoveID AS DepartureMoveID,
                ROW_NUMBER() Over(Partition by CASE WHEN sch_A.itemID IS NULL THEN Sch_D.itemID ELSE Sch_A.itemID END
                                    Order by Sch_A.moveTime) as RowNum
      FROM      Movements Sch_A
                FULL JOIN Movements Sch_D 
                    ON Sch_A.itemID = Sch_D.itemID 
                    AND Sch_A.LocID = Sch_D.LocID
                    AND ISNULL(Sch_A.ShelfID,0) = ISNULL(Sch_D.ShelfID,0)
                    AND ISNULL(Sch_A.AltShelfID,0) = ISNULL(Sch_D.AltShelfID,0)
                    AND Sch_A.MoveID != Sch_D.MoveID
                    AND Sch_A.moveTime <= Sch_D.moveTime
      WHERE     ( Sch_A.EventType != 'D' OR Sch_A.EventType IS NULL)
                AND ( Sch_D.EventType != 'A' OR Sch_D.EventType IS NULL)
)
SELECT * 
FROM CTE
WHERE RowNum = 1

StackExchange Database Administrators Q#45901, answer score: 2