Link to home
Start Free TrialLog in
Avatar of keithwilson1
keithwilson1Flag for United States of America

asked on

I am display first semester and second semester grades but if the grades are above 69 they should not show . I will show you the code and the results

SELECT DISTINCT Building,
          BuildingName,
          StudentId,
          --SECTION_KEY,
            PEIMS_Sequence,
            StudentName,
            CASE PEIMS_Sequence
            WHEN '0' THEN COURSE
            ELSE LEFT(COURSE,DATALENGTH(COURSE)-1)
            END
            AS COURSE1,
             COURSE,
            CourseName,
            CREDIT,
            SchoolYear,
            StudentGrade,
            SEM1Grade,
            SEM2Grade,
            MailingAddress,
            MailingCity,
            MailingState,
            MailingZip
            from #tmpRC
          where ((PEIMS_Sequence = '0') and ((ISNUMERIC(SEM1Grade) = 1  and  CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM1Grade) <> 1  and  CONVERT(varchar, SEM1Grade) = SEM1Grade)) or ((ISNUMERIC(SEM2Grade) = 1  and  CONVERT(int, SEM2Grade) <= 69) or (ISNUMERIC(SEM2Grade) <> 1  and  CONVERT(varchar, SEM2Grade) = SEM2Grade)))
            UNION
            SELECT DISTINCT Building,
          BuildingName,
          StudentId,
          --SECTION_KEY,
            PEIMS_Sequence,
            StudentName,
            CASE PEIMS_Sequence
            WHEN '0' THEN COURSE
            ELSE LEFT(COURSE,DATALENGTH(COURSE)-1)
            END
            AS COURSE1,
             COURSE,
            CourseName,
            CREDIT,
            SchoolYear,
            StudentGrade,
            SEM1Grade,
            SEM2Grade,
            MailingAddress,
            MailingCity,
            MailingState,
            MailingZip
            from #tmpRC
            where ((PEIMS_Sequence IN ('1','2')) and (SEM1Grade is NULL) or ((ISNUMERIC(SEM1Grade) = 1  and  CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM1Grade) <> 1  and  CONVERT(varchar, SEM1Grade) = SEM1Grade)) and (SEM2Grade is NULL) or ((ISNUMERIC(SEM2Grade) = 1  and  CONVERT(int, SEM2Grade) <= 69) or (ISNUMERIC(SEM2Grade) <> 1  and  CONVERT(varchar, SEM2Grade) = SEM2Grade)))
            UNION
            SELECT DISTINCT Building,
          BuildingName,
          StudentId,
          --SECTION_KEY,
            PEIMS_Sequence,
            StudentName,
            CASE PEIMS_Sequence
            WHEN '0' THEN COURSE
            ELSE LEFT(COURSE,DATALENGTH(COURSE)-1)
            END
            AS COURSE1,
             COURSE,
            CourseName,
            CREDIT,
            SchoolYear,
            StudentGrade,
            SEM1Grade,
            SEM2Grade,
            MailingAddress,
            MailingCity,
            MailingState,
            MailingZip
            from #tmpRC

            where      ((PEIMS_Sequence = '2') and ((ISNUMERIC(SEM1Grade) = 1) and CONVERT(int, SEM1Grade) < 69) and (SEM1Grade is NULL)) and ((ISNUMERIC([SEM2Grade]) = 1) and CONVERT(int, SEM2Grade) <= 100) and ((CONVERT(int, SEM1Grade) + CONVERT(int, SEM2Grade) / 2 < 70))

       --   AND StudentGrade IN (SELECT items FROM dbo.Split(@Grade,','))
            order by StudentGrade , StudentName
--      DROP TABLE #tmpMessage
--      DROP TABLE #tmpFilter
--      DROP TABLE #tmpTEA
--      DROP TABLE #tmpExceptions
--      DROP TABLE #tmpRC
--      DROP TABLE #tmpRCX
      
      
END
HS-Failure-Letter-Results02262017.x.xlsx
HS-Failure-Letter-Results02262017.x.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial