SELECT DISTINCT
	C.Student_ID,
	isnull(left(dbo.capfirst(rtrim([first_name])),12),' ') as First_Name, 
	isnull(left(dbo.capfirst(rtrim([last_name])),17),' ') as Last_Name, 
	LEFT(Middle_Name, 1) as Middle_Initial,
	RTRIM(CONVERT(CHAR,Birth_Date,101)) AS DOB,
	Mailing_Address + ' ' + Mailing_Apartment as Address,
	Mailing_City,  Mailing_Zip,
	C.School_Number,
	Grade_Level,
	isnull(Teacher.Teacher_Room,' ') AS HR,
    isnull (Teacher.Teacher_First_Name+' '+Teacher.Teacher_Last_Name,' ') AS Teacher_Name,
    
    Case
    When Father_First_Name<> ' ' THEN (Father_First_Name+' '+Father_Last_Name)
    When Father_First_Name= ' '  THEN (Mother_First_Name+' '+Mother_Last_Name)
    When Mother_First_Name=' ' THEN (Guardian_First_Name+' '+Guardian_Last_Name)
    END as Guardian,
    
    ISNULL(RTRIM(Home_Phone), '  ') as Home_Phone,
	isNUll(LUN.Lunch_Status,' ') as FR_Status,
	isnull(student_email, ' ') as Student_Email


  FROM Student_Info_Core C
	LEFT JOIN Student_Misc_Basic M
	ON C.School_Year = M.School_Year and C.Student_ID = M.Student_ID
	LEFT JOIN Student_Guardian G 
	ON C.School_Year = G.School_Year AND C.Student_ID = G.Student_ID
	LEFT JOIN Student_Residence R
	ON C.School_Year = R.School_Year and C.Student_ID = R.Student_ID
	LEFT JOIN Student_Transportation T
	ON C.School_Year = T.School_Year and C.Student_ID = T.Student_ID
	LEFT JOIN Student_Special S 
	ON C.School_Year = S.School_Year AND C.Student_ID = S.Student_ID
	LEFT JOIN Student_Language L 
	ON C.School_Year = L.School_Year AND C.Student_ID = L.Student_ID
	Join Teacher
	ON Homeroom_Teacher=Teacher.Teacher_SSN
	Join Student_Lunch LUN
	ON LUN.Student_ID=S.Student_ID

  WHERE C.School_Year='1112' AND C.Student_Status_A_W_P='A' AND
		 Teacher.School_Year='1112' AND LUN.School_Year='1112' AND
		 C.Grade_Level<>'30' and School_Number<>'0022'
		 AND C.Grade_Level<>'PK'
		 
	ORDER BY C.School_Number, C.Last_Name

school_Number<>'002' AND C.Grade_level<> 'PK'

((school_number = '0022' AND C.Grade_Level = 'PK') OR 
    (school_number <> '0022' AND C.Grade_Level <> 'PK'))

      and School_Number<>'0022'
      AND C.Grade_Level<>'PK'

      and (School_Number = '0022' OR C.Grade_Level<>'PK')