GF (9/21/2012)
What I am wanting to achieve is a list of any reports that have a current status of 1 (submitted), and that report has been previously submitted.In other words, I need a list of reports where the status =1 and the previous status =2 for that same reported.
Might not be exactly what you need but hopefully gives you some ideas.
DECLARE @Tmp TABLE (RowId INT IDENTITY(1,1), Report_id INT, Report_status_id INT, last_modified DATETIME)
INSERT INTO @Tmp (Report_id, Report_status_id, last_modified)
SELECTReport_id, Report_status_id, last_modified
FROMreporthistory
ORDER BY Report_id, last_modified DESC
SELECTt1.Report_id, t1.Report_status_id, t1.last_modified
, pv_Report_id = t2.Report_id, pv_Report_status_id = t2.Report_status_id, pv_last_modified = t2.last_modified
FROM@Tmp t1
INNER JOIN @Tmp t2 ON t1.Report_id = t2.Report_id AND t1.RowId + 1 = t2.RowId
WHEREt1.Report_status_id = 1
ANDt2.Report_status_id = 2
_____________________________________________________________________
- Nate