CREATE TABLE
#EmpDetails (
DetailID int identity(1,1),
EmpID int,
ChangeReason varchar(20),
Dept int,
DateStamp smalldatetime
)
INSERT INTO #EmpDetails (EmpID, ChangeReason, Dept, DateStamp)
SELECT 2135, 'New Hire', 011090, '1/15/2004 14:02' UNION
SELECT 2135, 'Merit', 011090, '3/02/2004 9:17' UNION
SELECT 2135, 'Merit', 011090, '4/20/2004 16:27' UNION
SELECT 2135, 'Dept Transfer', 011023, '6/10/2004 15:59' UNION
SELECT 2135, 'Merit', 011023, '8/27/2004 11:19' UNION
SELECT 2135, 'Dept Transfer', 011067, '9/29/2004 11:19' UNION
SELECT 2157, 'New Hire', 011023, '2/01/2004 15:03' UNION
SELECT 2192, 'New Hire', 011023, '1/15/2003 14:02' UNION
SELECT 2192, 'Merit', 011023, '3/02/2003 9:17' UNION
SELECT 2192, 'Merit', 011023, '4/20/2003 16:27' UNION
SELECT 2192, 'Dept Transfer', 011045, '6/10/2003 15:59' UNION
SELECT 2192, 'Merit', 011045, '8/27/2003 11:19' UNION
SELECT 2192, 'Dept Transfer', 011023, '9/29/2003 11:19'
SELECT * From #EmpDetails
SELECT
S1.EmpId,
OldDept,
NewDept,
TransDate
FROM
(
SELECT
E1.EmpId,
TransDate = E1.DateStamp,
NewDept = E1.Dept,
SeqNum = Count(*)
FROM
#EmpDetails E1
INNER JOIN #EmpDetails E2
ON E1.EmpId = E2.EmpID
AND E1.DateStamp >= E2.DateStamp
GROUP BY
E1.EmpID,
E1.Dept,
E1.DateStamp,
E1.ChangeReason
HAVING
E1.ChangeReason IN ('Dept Transfer', 'New Hire')
) S1 LEFT JOIN (
SELECT
E1.EmpId,
OldDept = E1.Dept,
SeqNum = Count(*)
FROM
#EmpDetails E1
INNER JOIN #EmpDetails E2
ON E1.EmpId = E2.EmpID
AND E1.DateStamp >= E2.DateStamp
GROUP BY
E1.EmpId,
E1.Dept,
E1.DateStamp
) S2 ON S2.EmpId = S1.EmpID AND S2.SeqNum = S1.SeqNum - 1
ORDER BY
S1.EmpId,
TransDate
DROP TABLE #EmpDetails