Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,altShelfID)
VALUES(6,3,'A','2013-01-16 09:00',2,NULL,7)

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

SET IDENTITY_INSERT movements off

Query

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
      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
                    )

Results returned have matches for the same schedule multiple times:

3   2013-01-05 09:00:00.000 2013-01-06 13:00:00.000 1   3   NULL    1   3   NULL    1   2
3   2013-01-05 09:00:00.000 2013-01-07 15:00:00.000 1   3   NULL    1   3   NULL    1   5
3   2013-01-05 09:00:00.000 2013-01-15 12:00:00.000 1   3   NULL    1   3   NULL    1   7
3   2013-01-07 09:00:00.000 2013-01-07 15:00:00.000 1   3   NULL    1   3   NULL    3   5
3   2013-01-07 09:00:00.000 2013-01-15 12:00:00.000 1   3   NULL    1   3   NULL    3   7
3   2013-01-15 09:00:00.000 2013-01-15 12:00:00.000 1   3   NULL    1   3   NULL    4   7
3   2013-01-16 09:00:00.000 NULL    2   NULL    7   NULL    NULL    NULL    6   NULL

What I would like to see is the following:

3   2013-01-05 09:00:00.000 2013-01-06 13:00:00.000 1   3   NULL    1   3   NULL    1   2
3   2013-01-07 09:00:00.000 2013-01-07 15:00:00.000 1   3   NULL    1   3   NULL    3   5
3   2013-01-15 09:00:00.000 2013-01-15 12:00:00.000 1   3   NULL    1   3   NULL    4   7
3   2013-01-16 09:00:00.000 NULL    2   NULL    7   NULL    NULL    NULL    6   NULL

How do I reduce the duplicate matches for schedules? Thanks.

Update: As Paul suggested below, I could try and make a correlation column to match items on the fly. What are the perils of doing something like the following:

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
  FROM      Movements Sch_A
            FULL JOIN Movements Sch_D 
                ON Sch_A.itemID = Sch_D.itemID
                    AND Sch_D.MoveID = (select top 1 MoveID from movements i 
                            where i.EventType = 'D' and Sch_A.movetime < i.movetime
                             AND Sch_A.itemID = i.itemID
                                            AND Sch_A.LocID = i.LocID
                                            AND ISNULL(Sch_A.ShelfID,
                                                      0) = ISNULL(i.ShelfID,
                                                      0)
                                            AND ISNULL(Sch_A.AltShelfID,
                                                      0) = ISNULL(i.AltShelfID,
                                                      0)
                                            AND Sch_A.MoveID != i.MoveID        
                                order by i.moveTime ASC)

  WHERE     ( Sch_A.EventType != 'D'
              OR Sch_A.EventType IS NULL
            )
            AND ( Sch_D.EventType != 'A'
                  OR Sch_D.EventType IS NULL
                )
share|improve this question
    
Which version of SQL-Server is this for? –  ypercube Jul 8 '13 at 15:14
    
MS SQL 2005, Standard Edition –  banesong Jul 8 '13 at 16:08

4 Answers 4

up vote 0 down vote accepted

A bit ugly but I could only do this with precalculation myself.

update movements 
set linkTime = (select top 1 movetime from movements i 
        where i.EventType = 'D' and movements.movetime < i.movetime
        and movements.locid = i.locid
        order by i.moveTime asc)
where movements.EventType = 'A'

select a.*, d.*
from (select * from movements
        where EventType = 'A') as a
left outer join (select * from movements
        where EventType = 'D') as d ON a.locid = d.locid AND a.linktime = d.movetime

Essentially I precalc the link you talk about in the UPDATE statement then use it to link as desired in the SELECT. I missed out some of the columns that had no relevance in the sample data (itemID etc.).

Would probably make more sense to insert into a #temporary table rather than permenantly add a new column to movements but that will depend on local knowledge ..

NB: linkTime is a new column of type datetime added to the table movements

share|improve this answer
    
This is on a production system with hundreds of thousands of rows; adding a column would require severe amounts of work, both from a CCB perspective and ongoing maintenance (e.g. a date change of the departure of the item). That being said, this might work with a bit of modification. I will update my question with a modified version. –  banesong Jul 8 '13 at 15:04
    
Like I said, just do it on a temporary table. I've done this with DataMart work myself on similar size tables. –  Paul Jul 8 '13 at 15:05
    
Due to security configurations on the server and standards enforcement, I am prohibited from using any DDL and temp tables. Good suggestion though. –  banesong Jul 8 '13 at 15:08
1  
Paul: this is a great solution when refactored as in my answer above. I have been able to match down arrivals and departures in a table holding ~32000 rows in under 3 seconds. I apologize as to how long it took to decide on the answer; I have had to spend some time refactoring the code to the database structure and testing it against production data draws. Thanks again! –  banesong Jul 11 '13 at 13:08

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
share|improve this answer
    
The trouble with that one is that I have to know which one I want in the first place. What I am looking for is a query that will make the matches to the most chronologically consistent entry (1 with 2, 3 with 5, 4 with 7 and 6 with none) and then stop matching that row. For example, once I know that I have made a match with 1 and 2, discard them from any further matches; this would make the matches on 1 and 5 and 1 and 7 not show. –  banesong Jul 8 '13 at 14:17

This is for SQL-Server 2012 only*:

; WITH cte AS
  ( SELECT ItemID, LocID, ShelfID, altShelfID, 
           moveTime, EventType, MoveID,
           rta = COUNT(CASE WHEN EventType = 'A' THEN 1 END) OVER 
                 ( PARTITION BY ItemID, LocID, ShelfID, altShelfID
                   ORDER BY moveTime, EventType, MoveID
                   ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND CURRENT ROW
                 )
    FROM Movements
  ), 
ctea AS
  ( SELECT *  FROM cte  WHERE EventType = 'A' ),
cted AS
  ( SELECT *  FROM cte  WHERE EventType = 'D' )
SELECT 
    ItemID = COALESCE(a.ItemID, d.ItemID),
    ArrivalTime   = a.moveTime, 
    DepartureTime = d.moveTime, 
    LocID = COALESCE(a.LocID, d.LocID), 
    ShelfID = COALESCE(a.ShelfID, d.ShelfID), 
    altShelfID = COALESCE(a.altShelfID, d.altShelfID), 
    ArrivalID = a.MoveID,
    DepartureID = d.MoveID
FROM ctea AS a
  FULL JOIN cted AS d
    ON  a.ItemID = d.ItemID
    AND a.LocID = d.LocID
    AND (     a.ShelfID = d.ShelfID 
          AND a.altShelfID IS NULL
          AND d.altShelfID IS NULL
       OR     a.ShelfID IS NULL
          AND d.ShelfID IS NULL
          AND a.altShelfID = d.altShelfID
        )
    AND a.rta = d.rta 
ORDER BY 
    ItemID, 
    COALESCE(d.moveTime, a.moveTime) ;

Tested at SQL-Fiddle

*: (saw the tag for 2005 too late.)
Judging from this great article by @Aaron Bertrand, which has thorough tests of various methods to calculate running totals, a similar problem to what you have here, I'd try a cursor-based solution for this problem in 2005 version: Best approaches for running totals – updated for SQL Server 2012

share|improve this answer
    
Thanks anyway. At some point we will get to a modern version of MSSQL. –  banesong Jul 8 '13 at 16:23

I added an additional condition to the ON clause. Not sure how efficient this will be and it may have a problem if there are two identical arrival or departure times for a given item/location. But it does work for the data given.

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
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
    AND Sch_D.moveTime = (SELECT MIN(Sch_D2.moveTime) FROM Movements Sch_D2
                        WHERE Sch_A.itemID = Sch_D2.itemID
                            AND Sch_A.LocID = Sch_D2.LocID
                            AND ISNULL(Sch_A.ShelfID,0) = ISNULL(Sch_D2.ShelfID,0)
                            AND ISNULL(Sch_A.AltShelfID,0) = ISNULL(Sch_D2.AltShelfID,0)
                            AND Sch_A.MoveID != Sch_D2.MoveID
                            AND Sch_A.moveTime <= Sch_D2.moveTime)
WHERE
    ( Sch_A.EventType != 'D'
        OR Sch_A.EventType IS NULL
    )
    AND 
    ( Sch_D.EventType != 'A'
        OR Sch_D.EventType IS NULL
    )

EDIT:

Same idea but using CTEs to handle the ISNULLs and WHERE clauses in a single place. That and I think it looks a bit cleaner this way too.

WITH Sch_A AS (
        SELECT MoveID, ItemID, EventType, moveTime, LocID,
            ISNULL(ShelfID,'') ShelfID, ISNULL(altShelfID,'') altShelfID
        FROM Movements
        WHERE EventType = 'A'
            OR EventType IS NULL),
    Sch_D AS (
        SELECT MoveID, ItemID, EventType, moveTime, LocID,
            ISNULL(ShelfID,'') ShelfID, ISNULL(altShelfID,'') altShelfID
        FROM Movements
        WHERE EventType = 'D'
            OR EventType IS NULL)
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
FROM Sch_A
FULL JOIN Sch_D 
    ON Sch_A.itemID = Sch_D.itemID
    AND Sch_A.LocID = Sch_D.LocID
    AND Sch_A.ShelfID = Sch_D.ShelfID
    AND Sch_A.AltShelfID = Sch_D.AltShelfID
    AND Sch_A.MoveID != Sch_D.MoveID
    AND Sch_A.moveTime <= Sch_D.moveTime
    AND Sch_D.moveTime = (SELECT MIN(Sch_D2.moveTime) FROM Movements Sch_D2
                        WHERE Sch_A.itemID = Sch_D2.itemID
                            AND Sch_A.LocID = Sch_D2.LocID
                            AND Sch_A.ShelfID = Sch_D2.ShelfID
                            AND Sch_A.AltShelfID = Sch_D2.AltShelfID
                            AND Sch_A.MoveID != Sch_D2.MoveID
                            AND Sch_A.moveTime <= Sch_D2.moveTime)
share|improve this answer
    
Just curious, would that be more or less efficient than the edited suggestion in the main question? It seems that if we use the MoveID field rather than time as the JOIN ON statement, and then have all the matching done in the subquery in the join itself, we would not have to have in essence the same mathes (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)) done twice. I would think that using the ISNULL 4 times would increase the time to process over a large table. –  banesong Jul 8 '13 at 16:07
    
I'm honestly not sure. I would give both versions a try with some test data and see how it works out. I did edit my answer to use CTEs to push the ISNULLs and WHERE code to a single place. Looks cleaner I think too. –  Kenneth Fisher Jul 8 '13 at 16:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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