SQl Query Help.

If sql 2012 then use this

SELECT PKID,ID,[Date],RoleId,Status
FROM
(
SELECT *,LAG(Status,1,NULL) OVER (PARTITION BY ID ORDER BY [Date]) AS PrevStatus
FROM Table
)t
WHERE PrevStatus = 'Close'
AND STatus = 'Approved'

If SQL 2008 R2 or below use this

;With CTE
AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Date]) AS Rn
FROM Table
)

SELECT c1.PKID,c1.ID,c1.[Date],c1.RoleId,c1.Status
FROM CTE c1
INNER JOIN CTE c2
ON c2.ID = c1.ID
AND c2.Rn = c1.Rn -1
WHERE c2.Status = 'Close'
AND c1.STatus = 'Approved'

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c