Hello,
I'm thinking I need a case statement but I'm not sure where to put it. So far I have:
CASE WHEN ISNUMERIC(a.grade) = 1 THEN b.GradeType = 'Numeric'
else b.GradeType = 'Alpha'
End
I have two tables: table A has studentID, class, and grade. Table B has GradeType and Average.
Table A
StudentID class grade
125 003 75
456 004 82
854 005 A
Table B
GradeType Average
Numeric 89
Alpha 4
GradeType is either Numeric or Alpha. My problem is some grades are Numeric and some are Alpha so when a grade is Numeric (or Alpha) it needs to also pick up what ever the average is for Numeric (or Alpha). I'm not sure how to accomplish this. I was thinking a case statement should be in either the from in the join or the where clause. If anyone could point me in the right direction. Thanks!
First off, it is considered bad practice to mix data types in one column. It would be better to have two columns: one for numeric grades and another for alphabetic grades. Actually the second column can be computed from the first.
Here's an example of a way to do that:
declare @TableA table (StudentID int, class char(3), grade_numeric int, grade_alpha char(2))
insert into @TableA(StudentID, class, grade_numeric, grade_alpha) values
--StudentID class grade
(125, '003', 75, null),
(456, '004', 82, null),
(854, '005', null, 'A')
;
with cte as
(
select StudentID, class
, case when grade_numeric is null and grade_alpha is not null
then case grade_alpha
when 'A' then 98
when 'B' then 88
when 'C' then 78
when 'D' then 68
when 'E' then 55
else 0
end
else grade_numeric
end as grade_numeric
, case when grade_numeric is not null and grade_alpha is null
then case
when grade_numeric > 92 then 'A'
when grade_numeric > 82 then 'B'
when grade_numeric > 72 then 'C'
when grade_numeric > 62 then 'D'
when grade_numeric > 52 then 'E'
else 'F'
end
else grade_alpha
end as grade_alpha
from @TableA
)
select * from cte
Not quite what I'm looking for but, thanks! I have no control over the database or what the teachers put into it. I just need the corresponding average to whether its a numeric grade or an alpha grade. I have updated it to:
CASE WHEN ISNUMERIC(a.grade) = 1 THEN (select b.gradetype from table b where b.GradeType='Numeric')
else (select b.gradetype from table b where b.GradeType='Alpha')
End,
Its in the select statement but now I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
You should scold your teachers for giving you a bad design to work with!
However, you can use what I posted to build your final query showing the averages.
1 Like
Note that ISNUMERIC is not reliable. Better to use
NOT LIKE '%[^0-9]%'
instead