DECLARE @test-2 TABLE

(

RowID INT,

DateStamp DATE,

PrevStatus INT,

CurrentStatus INT

);

INSERT INTO @test-2

(RowID, DateStamp, PrevStatus, CurrentStatus)

VALUES

(1, '6/1/2012', NULL, 8),

(2, '6/2/2012', 8, 9),

(3, '6/8/2012', 9, 8),

(4, '6/10/2012', 8, 9),

(5, '6/12/2012', 9, 8),

(6, '6/16/2012', 8, 9),

(7, '6/19/2012', 9, 8);

SELECT * INTO #TEST1 FROM

(

SELECT *

FROM @test-2

WHERE currentstatus = 8

AND PrevStatus IS NOT NULL

)A

select * from #TEST1

;WITH CTE AS

(

SELECT *, ROW_NUMBER() OVER (ORDER BY DateStamp) AS RowNumber

FROM #TEST1

)

SELECT A.RowID,B.rowid,DATEDIFF(dd, A.DateStamp, B.DateStamp) as 'DATEPART'

FROM CTE A

JOIN CTE B

ON A.RowNumber = B.RowNumber - 1

DROP TABLE #TEST1