select distinct  stateid as id_state, student.personID as district_student_id, studentNumber as number,
firstName as first_name,
cast(replace(middleName,'"',' ') as varchar(60)) as middle_name,
cast(replace(lastname,',',' ') as varchar(60))as last_name,
suffix as suffix, 
birthdate, 
case 
when enrollment.englishProficiency <=5 then 1
else null
end  as esl, 
enrollment.specialedstatus as special_ed
from student 
left outer join enrollment on
enrollment.active=1 and enrollment.enddate is null and enrollment.personid = student.personid


where activeyear = 1

