Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    22

    Unanswered: Filtered Where Clause

    I need to include pre k kids in the is script for one school and exclude them for the others. I have them all excluded with the last statement in the where clause. How would I go about accomplishing this.

    Thanks
    Alex G.


    Code:
    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

  2. #2
    Join Date
    Feb 2012
    Posts
    7
    The following filter should work...

    remove

    Code:
    school_Number<>'002' AND C.Grade_level<> 'PK'
    and replace with


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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would replace
    Code:
          and School_Number<>'0022'
          AND C.Grade_Level<>'PK'
    with
    Code:
          and (School_Number = '0022' OR C.Grade_Level<>'PK')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •