student Gradejump in a historical data with the specific date they had gradejump

I have a historical table holding daily student record. I want to get which date they had change of grade i.e from grade 3a to 3b as jump1 and from 3b to 5 jump2 with the relevant date. below is the script- problem with my script I dont want all the rows that there is no jump. “”

WITH Grade1 AS(
SELECT DISTINCT  Employee as StudentID,BAND as Grade ,CensusDate AS GradeDate
FROM Gradehistory WHERE Band IS NOT NULL
GROUP BY  Employee,BAND ,CensusDate
),
Grade2 AS(
SELECT DISTINCT   Employee as StudentID,BAND as Grade ,CensusDate AS GradeDate
FROM Gradehistory WHERE Band IS NOT NULL
GROUP BY Employee, band , CensusDate
)
SELECT distinct A.StudentID,A.Grade as lowGrade ,B.Grade as Jumpgrade, B.GradeDate as JumpGradeDate
FROM Grade1 A INNER JOIN Grade2 B ON A.StudentID=B.StudentID
WHERE A.Grade < B.Grade 
GROUP BY A.StudentID, A.Grade, B.Grade, B.GradeDate
ORDER BY A.StudentID, a.Grade, JumpGradeDate ASC 

StudentID   lowGrade    Jumpgrade   JumpGradeDate
Ali 3   4   01/01/2017
Ali 3   4   01/06/2017
Sam 5   6   01/01/2017
Sam 5   6   01/03/2017
Sam 5   6   01/10/2019
Joe 6   7   01/06/2017
Joe 6   7   01/07/2017
Joe 6   7   01/08/2017
Joe 6   7   01/10/2018
Joe 6   7   01/08/2020
Joe 6   8A  01/09/2017
Joe 6   8A  01/07/2018
Joe 6   8A  01/08/2018
Joe 6   8A  01/09/2018
Joe 6   8A  01/09/2020
Joe 6   8A  01/10/2020
Joe 6   8A  01/11/2020
Joe 7   8A  01/09/2017
Joe 7   8A  01/03/2018
Joe 7   8A  01/12/2020
Joe 7   8A  01/01/2021
Joe 7   8A  01/02/2021
Joe 7   8A  01/03/2021
        

all I need the specific date the student had jump Jo should have only 2 rows jump 7 and jump 8a
hope explained it properly please ask me if I have not.
Many Thanks

Answer

I want to get which date they had change of grade i.e from grade 3a to 3b as jump1 and from 3b to 5 jump2 with the relevant date.

This sounds like conditional aggregation with lag():

select studentid,
       min(case when grade = '3b' and prev_grade = '3a' then censusdate end) as trans_3b_from_3a,
       min(case when grade = '5' and prev_grade = '3b' then censusdate end) as trans_5_from_3b
from (select gh.*,
             lag(grade) over (partition by studentid order by censusdate) as prev_grade
      from Gradehistory gh
      where band is not null
     ) gh
group by studentid;

If you don’t really care about the transition but only want the earliest date for each band, then this is even simpler:

select studentid,
       min(case when grade = '3b' then censusdate end) as trans_3b_from_3a,
       min(case when grade = '5' then censusdate end) as trans_5_from_3b
from Gradehistory gh
where band is not null
group by studentid