keithwilson1
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(COU RSE)-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(COU RSE)-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(COU RSE)-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
BuildingName,
StudentId,
--SECTION_KEY,
PEIMS_Sequence,
StudentName,
CASE PEIMS_Sequence
WHEN '0' THEN COURSE
ELSE LEFT(COURSE,DATALENGTH(COU
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(COU
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(COU
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.