How to choose the right row

Transact-SQL
1

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!

2

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
3

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.

4

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
5

Note that ISNUMERIC is not reliable. Better to use

NOT LIKE '%[^0-9]%'

instead

6

Thanks! I will try this.