Something like this?
DECLARE @CurrentTerm VARCHAR(10) = 'Term5';
SELECT studentID, PrevFailTerm = MAX(Term), CurrentTerm = @CurrentTerm
FROM myTable
WHERE Term < @CurrentTerm AND Result = 'Fail'
GROUP BY studentID;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP