select 
reg.DISTRICT as 'DistrictID', BUILDING as 'CampusID',RTRIM(reg.STUDENT_ID) as 'StudentID', 
FIRST_NAME as 'FirstName', MIDDLE_NAME as 'MiddleName', LAST_NAME as 'LastName', 
GENDER, GRADE as 'Grade Level', SECTION_504_PLAN as '504',
--special education, lep(duplicated in demographics file)
 (case when (r2.FIELD_VALUE='' or r2.FIELD_VALUE is null) then 'N' else r2.FIELD_VALUE end)   as 'Special Ed',
 (case when (r1.FIELD_VALUE='1' ) then '1' else '0' end) as 'LEP' 
 , r3.FIELD_VALUE as 'TSDS'
from reg

left join REG_PERSONAL on reg.STUDENT_ID=REG_PERSONAL.STUDENT_ID

left join REG_USER r2 on (r2.STUDENT_ID=reg.STUDENT_ID)and (r2.SCREEN_NUMBER='148' and r2.FIELD_NUMBER='4')
left join REG_USER r1 on (r1.STUDENT_ID=reg.STUDENT_ID)and(r1.SCREEN_NUMBER='146' and r1.FIELD_NUMBER='5')
left join REG_USER r3 on (r3.STUDENT_ID=reg.STUDENT_ID)and(r3.SCREEN_NUMBER='10165' and r3.FIELD_NUMBER='1')

where CURRENT_STATUS='A'
and grade not in ('EE', 'PK', 'K', 'KG')