I wonder if this enumeration is possible using SQL. The grouping is by id and year, if the grade is less than 9, I want to assign a 0 else if the grade is in (9, 10,11,12, SS), I would like the count to start at 1 for the first time the grade is in (9, 10,11,12, SS) and increase by 1 as the year increases. Not sure how to achieve this.

id      year    grade   want
11724   2009/10 6       0
11724   2010/11 7       0
11724   2011/12 8       0
11724   2012/13 9       1
11724   2013/14 10      2
11724   2014/15 11      3
11724   2015/16 12      4
21193   2009/10 9       1
21193   2010/11 10      2
21193   2011/12 11      3
21193   2012/13 12      4
21193   2013/14 12      5
21193   2014/15 SS      6
21288   2009/10 10      1
21288   2010/11 11      2
21288   2011/12 12      3
21288   2012/13 12      4
21288   2013/14 12      5
21288   2014/15 12      6
21314   2009/10 10      1
21314   2010/11 11      2
21314   2011/12 12      3
21314   2012/13 SS      4
21314   2013/14 SS      5
21314   2014/15 SS      6
22442   2009/10 9       1
22442   2010/11 10      2
22442   2011/12 11      3
22442   2012/13 SS      4
22442   2013/14 SS      5
22442   2014/15 SS      6
22442   2015/16 12      7

I am expecting the results in the want column.

This is what I have tried.

SELECT id, [Year],grade,
       SUM(flag) OVER (PARTITION BY id 
                       ORDER BY id, [Year]) + 1 AS want
FROM (
  SELECT id, [Year],grade,
         IIF(LAG([Year]) OVER (PARTITION BY id 
                                ORDER BY id, [Year]) <> [Year], 1, 0) AS flag
  FROM mytable) AS t
ORDER BY id, [Year]