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]
New contributor
Twildone is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1