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

asked on

Have a conversion issue with varchar to int in a SQL: Query.

Course should display if the course has a REIMS Sequence = 1 or 2 and the
student has a Semester 1 grade below a 70 and the Semester 2 grade that
when added to the Semester 1 grade and divided by 2 is not equal to a 69.5
or higher. A/B courses should be linked together to display on the same line,
i.e. ENG 104A-1 = 55 and Eng 104B-1 = 71 - 55+71=126+2= 63
Both the 55" for SEM 1 and the "71 for SEM 2 should display on the letter
[4.0.3]
Course should display if the course has a REIMS Sequence = "1 or 2" and the
student has either a Semester 1 or Semester 2 grade that is < 69. A/B courses
should be linked together to display on the same line,
i.e., ENG 104A-1 = "65 and Eng 104B-1 = "68" - Both the "65" and the 68"
should display on the letter on the same line.
[4.0.4]
Course should display if the course has a REIMS Sequence = 0 and the
student has a failing grade < 69 for either semester, i.e. Govt or Eco - Govt -
0321-1 Semester 1 = 59 and Eco - 0331-1 Semester 2 = 64 - Both Govt and
Eco should display on the letter



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 NEWCRSENAME,
             COURSE,
            CourseName,
            CREDIT,
            SchoolYear,
            StudentGrade,
            SEM1Grade,
            SEM2Grade,
            MailingAddress,
            MailingCity,
            MailingState,
            MailingZip
            from #tmpRCX
       where ((PEIMS_Sequence = '0') and ((ISNUMERIC(SEM1Grade) = 1  and  CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM1Grade) <> 1  and  CONVERT(varchar, SEM1Grade) = SEM1Grade)) or ((ISNUMERIC(SEM1Grade) = 1  and  CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM2Grade) <> 1  and  CONVERT(varchar, SEM2Grade) = SEM2Grade))) OR
              ((PEIMS_Sequence = '1' or PEIMS_Sequence = '2') and ((SEM1Grade IS NULL) and (ISNUMERIC(SEM1Grade) = 1  and  CONVERT(int, SEM1Grade) <= 69) or (ISNUMERIC(SEM1Grade) <> 1  and  CONVERT(varchar, SEM1Grade) = SEM1Grade)) or ((SEM1Grade IS NULL) and (ISNUMERIC(SEM2Grade) = 1  and  CONVERT(int, SEM2Grade) <= 69) or (ISNUMERIC(SEM2Grade) <> 1  and  CONVERT(varchar, SEM2Grade) = SEM2Grade))) OR
              ((PEIMS_Sequence = '1' or PEIMS_Sequence = '2') and ((ISNUMERIC(SEM1Grade) = 1) and (CAST(SEM1Grade AS INT) > 70)) and ((ISNUMERIC([SEM2Grade]) = 1) and (CAST(SEM2Grade AS INT) <= 100)) and ((SEM1Grade + SEM2Grade) / 2 < 69.5)) AND
                  StudentGrade = ISNULL(@Grade,StudentGrade)
       order by StudentGrade, StudentName
HS-Failure-Letter-Input-Data-for-EE.xlsx
HS-Failure-Letter-Input-Data-for-EE.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