MS SQL：3つのテーブルを結合し、数値のみから平均を計算する

最後のステートメントとして注文する必要があります。 また、非数値をすべてavg関数で使用する前に除外する必要があります。
たぶんこのようなもの

declare @students table(person_id int, enroll_period int)
declare @grades table(person_id int, grade varchar(2))
insert into @students values (12401, 109), (43245, 112)
insert into @grades values(12401, '4'), (12401, '7'), (12401, 'B'), (43245, '12'), (43245, 'IB')
select s.PERSON_ID, 
       s.ENROLL_PERIOD,
       avg( case when isnumeric(g.grade) = 1 then convert(int, g.grade) else null end) as AvgGrade
from   @students s
  INNER JOIN @grades g on s.PERSON_ID = g.PERSON_ID
where  ENROLL_PERIOD IS NOT NULL AND ENROLL_PERIOD <> ''
GROUP BY s.PERSON_ID, ENROLL_PERIOD
Order by ENROLL_PERIOD ASC

これにより、次の結果が返されます

PERSON_ID   ENROLL_PERIOD   AvgGrade    
---------   -------------   ------- 
12401       109             5   
43245       112             12 

編集：
実際のテーブルでは、おそらく次のようになります。

select s.PERSON_ID, 
       s.ENROLL_PERIOD,
       avg( case when isnumeric(g.grade) = 1 then convert(int, g.grade) else null end) as AvgGrade
from   students s
  INNER JOIN grades g on s.PERSON_ID = g.PERSON_ID
where  ENROLL_PERIOD IS NOT NULL AND ENROLL_PERIOD <> ''
GROUP BY s.PERSON_ID, ENROLL_PERIOD
Order by ENROLL_PERIOD ASC

並べ替える前に、group by構文を配置する必要があります。

https://blog.sqlauthority.com/2007/06/14/sql-server-easy-sequence-of-select-from-join-where-group-by-having-order-by/からの引用

SQL構文の順序：

   SELECT yourcolumns
    FROM tablenames
    JOIN tablenames
    WHERE condition
    GROUP BY yourcolumns
    HAVING aggregatecolumn condition
    ORDER BY yourcolumns

select students.PERSON_ID, students.ENROLL_PERIOD, AVG(Cast(grades.GRADE as numeric))
from Students
INNER JOIN Grades on Students.PERSON_ID = Grades.PERSON_ID
where ENROLL_PERIOD IS NOT NULL AND ENROLL_PERIOD <> ''
GROUP BY students.PERSON_ID,students.ENROLL_PERIOD
Order by students.ENROLL_PERIOD ASC

注文はグループの後にする必要があります