keithwilson1
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(COU RSE)-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
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(COU
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.