SQL to migrate bad table design to good table design?

Question

I am working to convert an old Access database to SQL Server. All has been fairly straightforward in normalizing the database, but I have come upon an obstacle I can't seem to cross.

The old database uses a table to hold the movements of objects between boxes. The problem is that the old table does not hold the new box that the object was moved to, just a log of all the old boxes. The current box is held in the Obj table. Check this fiddle, http://sqlfiddle.com/#!6/382bb/7/0

Is there an elegant solution to this?

TL:DR;

The old table is returning data like this,

MoveID ObjectID   OldBoxID DTMoved
1      27         22       2009-09-11
2      27         16       2008-01-19
3      27         29       2004-10-31

I want it to return,

MoveID ObjectID   OldBoxID NewBoxID   DTMoved
1      27         22       24**      2009-09-11
2      27         16       22        2008-01-19
3      27         29       16        2004-10-31

**This value will be from the Obj table that shows the current BoxID for the object.

In case the fiddle is gone, here is my schema,

CREATE TABLE Box (
[BoxID] int IDENTITY(1,1) NOT NULL,
[BoxName] varchar(20) NULL,
CONSTRAINT [PK_Box] PRIMARY KEY CLUSTERED 
(
    [BoxID] ASC
))
GO

INSERT INTO Box (BoxName) VALUES ('Box1')
INSERT INTO Box (BoxName) VALUES ('Box2')
INSERT INTO Box (BoxName) VALUES ('Box3')
INSERT INTO Box (BoxName) VALUES ('Box4')
INSERT INTO Box (BoxName) VALUES ('Box5')
INSERT INTO Box (BoxName) VALUES ('Box6')
INSERT INTO Box (BoxName) VALUES ('Box7')
INSERT INTO Box (BoxName) VALUES ('Box8')
INSERT INTO Box (BoxName) VALUES ('Box9')
INSERT INTO Box (BoxName) VALUES ('Box10')

CREATE TABLE Obj (
    [ObjectID] int IDENTITY(1,1) NOT NULL,
    [ObjectName] varchar(20) NULL,
    [CurrentBoxID] int NULL
CONSTRAINT [PK_Obj] PRIMARY KEY CLUSTERED 
(
[ObjectID] ASC
))
GO

INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('tiger',3)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('file',8)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('press',9)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('heap',4)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('careful',6)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('fairies',5)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('uneven',7)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('fertile',5)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('brass',9)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('hospitable',8)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('stem',1)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('pastoral',9)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('strip',10)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('spiteful',9)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('scribble',6)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('vest',1)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('soothe',5)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('sin',4)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('fanatical',10)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('scissors',7)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('rat',3)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('plastic',5)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('store',6)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('mighty',1)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('juice',6)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('angry',8)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('zephyr',2)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('decorous',1)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('stretch',4)
INSERT INTO Obj (ObjectName,CurrentBoxID) VALUES ('complete',9)

CREATE TABLE Move (
    [MoveID] int IDENTITY(1,1) NOT NULL,
    [ObjectID] int NULL,
    [OldBoxID] int NULL,
    [DTMoved] datetime NULL
 CONSTRAINT [PK_Move] PRIMARY KEY CLUSTERED 
(
    [MoveID] ASC
))
GO

INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (5,10,'01/15/2006')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (30,6,'05/21/2008')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (27,10,'06/12/2010')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (25,6,'10/30/2006')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (19,10,'08/30/2008')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (19,2,'05/01/2006')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (29,4,'01/24/2013')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (16,10,'04/08/2008')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (17,3,'03/11/2014')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (3,4,'02/25/2006')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (17,9,'06/05/2008')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (27,5,'12/31/2008')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (11,8,'01/08/2012')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (27,1,'09/21/2012')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (21,7,'07/22/2017')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (16,1,'02/19/2005')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (12,3,'09/12/2012')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (3,3,'05/08/2016')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (6,1,'09/12/2006')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (20,1,'12/13/2015')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (13,4,'10/20/2017')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (2,5,'06/21/2015')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (6,5,'11/21/2008')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (15,5,'09/10/2005')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (15,10,'10/20/2006')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (10,10,'06/07/2008')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (9,2,'10/19/2012')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (3,1,'10/27/2011')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (27,1,'03/03/2010')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (2,8,'12/06/2013')
INSERT INTO Move (ObjectID,OldBoxID,DTMoved) VALUES (2,4,'03/29/2017')

I am looking for a query to return the following structure,

MoveID,ObjectID,DTMoved,OldBox,NewBox
12,27,'2008-12-31',Box5,Box1
29,27,'2010-03-03',Box1,Box10
3,27,'2010-06-12',Box10,Box1
14,27,'2012-09-21',Box1,Box2

Here are the queries I was using to show the examples in the fiddle,

--Move joins
SELECT MoveID, M.ObjectID, DTMoved, BB.BoxName AS OldBox, B.BoxName AS CurrentBox
FROM Move M
   INNER JOIN Obj O ON M.ObjectID = O.ObjectID
   INNER JOIN Box B ON O.CurrentBoxID = B.BoxID
   INNER JOIN Box BB ON M.OldBoxID = BB.BoxID
ORDER BY O.ObjectID, DTMoved

--How many moves per box
SELECT ObjectID, COUNT(ObjectID)
FROM Move
GROUP BY ObjectID
ORDER BY COUNT(ObjectID) DESC

--Look at Object ID 27
SELECT MoveID, M.ObjectID, DTMoved, BB.BoxName AS OldBox, '' AS NewBox, B.BoxName AS CurrentBox
FROM Move M
   INNER JOIN Obj O ON M.ObjectID = O.ObjectID
   INNER JOIN Box B ON O.CurrentBoxID = B.BoxID
   INNER JOIN Box BB ON M.OldBoxID = BB.BoxID
WHERE M.ObjectID = 27
ORDER BY DTMoved DESC

Answer 1

Try This :-

SELECT MoveID, M.ObjectID, DTMoved, BB.BoxName AS OldBox 
,isnull(lead(oldboxid) OVER (ORDER BY DTMoved), O.CurrentBoxID) As newBoxId
FROM Move M
   INNER JOIN Obj O ON M.ObjectID = O.ObjectID
   INNER JOIN Box BB ON M.OldBoxId = BB.BoxID
WHERE M.ObjectID = 27
ORDER BY DTMoved