compare with prev records:

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  October 20, 2014 at 11:40 pm

  #294399

  Hi,

  i am having a table which contains data of students like:

  StudentID,StudentName,Term,RESult.

  Sample data :

  StudentID,StudentName,Term,RESult.

  1,ABC,Term1,Pass

  1,ABC,Term2,Fail

  1,ABC,Term3,Pass

  1,ABC,Term4,Pass

  1,ABC,Term5,Pass

  Now i want to compare Result and dislay prevterm where student fail:

  now my output would be as:Now i want to compare latest term i.e. Term5 with prev Terms and if found Mismatch in result then i want to display as below:

  studentID PrevFailTerm, CurrentTerm

  1,Term2,Term5

  Thanks,

  Abhas

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  October 21, 2014 at 12:41 am

  #1753664

  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