This will do it:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable

(

[ReportHistoryID] [int] IDENTITY(1,1) NOT NULL

,[Report_id] [int] NOT NULL

,[Report_status_id] [int] NOT NULL

,[modified_by] [int] NOT NULL

,[last_modified] [datetime] NOT NULL DEFAULT (GETDATE())

,PRIMARY KEY (ReportHistoryID)

,UNIQUE (ReportHistoryID)

)

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Jul 31 2008 1:31AM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,2,15387,'Dec 3 2003 10:27AM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Dec 15 2003 7:09AM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,2,15387,'Dec 9 2003 5:41PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Dec 13 2003 11:37AM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (2,1,16836,'Dec 11 2003 10:52AM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (2,2,16836,'Dec 11 2003 5:06PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,1,17509,'Dec 19 2003 8:49PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,2,17509,'Dec 12 2003 12:23PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,1,17509,'Dec 12 2003 1:40PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,2,17509,'Dec 12 2003 2:32PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,1,17221,'Dec 12 2003 1:51PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,2,17221,'Dec 12 2003 2:22PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,1,17221,'Dec 12 2003 2:30PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 12 2003 3:53PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 12 2003 4:24PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 16 2003 2:27PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 16 2003 3:38PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 18 2003 2:10PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 19 2003 12:23PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 19 2003 3:17PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (6,1,18019,'Dec 19 2003 3:05PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (7,1,17111,'Dec 19 2003 4:58PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (8,1,18010,'Dec 19 2003 5:34PM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (9,1,17773,'Dec 22 2003 10:35AM')

INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (9,2,17773,'Dec 23 2003 5:05AM')

SELECT

Sub1.ReportHistoryID

,t1.Report_id

,t1.Report_status_id

,t2.Report_status_id AS Prev_status_id

,t1.modified_by

,t1.last_modified

FROM

(

SELECT

MAX(rh1.ReportHistoryID) AS ReportHistoryID

FROM

#TempTable AS rh1

GROUP BY

rh1.modified_by

) AS Sub1

INNER JOIN

#TempTable AS t1

ON Sub1.ReportHistoryID = t1.ReportHistoryID

INNER JOIN

#TempTable AS t2

ON t1.ReportHistoryID = t2.ReportHistoryID + 1

WHERE

t1.Report_status_id = 1

AND t2.Report_status_id = 2

Output:

ReportHistoryIDReport_idReport_status_idPrev_status_idmodified_bylast_modified

5112153872003-12-13 11:37:00.000

14412172212003-12-12 14:30:00.000

21512172572003-12-19 15:17:00.000