How to get varchar value and numerical value with conditions in SQL Server

Question

This is my table

create table Marks_Entry 
(
    id int, 
    std_id varchar(50),
    sub_id varchar(50),
    ASG_MARK varchar(50),
    TH_MARKS varchar(50),
    PR_MARCHAR varchar(50)
)

This table contains values like:

id std_id  sub_id   ASG_MARK  TH_MARKS   PR_MARCHAR
---------------------------------------------------
1  123     1        20        50     30
2  123     2        18        40     20
3  123     3        20        AB     30
4  123     4        AB        40     20
5  123     5        AB        AB     AB
6  123     6        0         0      0

I want to see my data like this:

id     std_id  sub_id  ASG_MARK TH_MARKS PR_MARCHAR  total  GRADE  
-----------------------------------------------------------------
1   123 1   20  50   30      100     A1
2   123 2   20  0    30      50      F1
3   123 3   18  40   20      78      C1
4   123 4   AB  40   20      60      E1
5   123 5   AB  AB   AB      AB      AB
6   123 6   0   0    0       0       0

My code is below, please see any one and help me...

select 
    id, STD_ID, SUB_ID, ASG_MARK, TH_MARKS, PR_MARCHAR,
    (ASG_MARK + TH_MARKS + PR_MARCHAR ) as total,
    CASE 
       WHEN (ASG_MARK + TH_MARKS + PR_MARCHAR) BETWEEN 91 AND 100 
          THEN 'A1' 
       WHEN (ASG_MARK + TH_MARKS + PR_MARCHAR) BETWEEN 81 AND 90 
          THEN 'B1' 
       WHEN (ASG_MARK + TH_MARKS + PR_MARCHAR) BETWEEN 71 AND 80 
          THEN 'C1' 
       WHEN (ASG_MARK + TH_MARKS + PR_MARCHAR) BETWEEN 61 AND 70 
          THEN 'D1' 
       WHEN (ASG_MARK + TH_MARKS + PR_MARCHAR) BETWEEN 51 AND 60 
          THEN 'E1'
       WHEN (ASG_MARK + TH_MARKS + PR_MARCHAR) BETWEEN 41 AND 50 
          THEN 'F1' 
       ELSE 'AB'
    END AS GRADE
from  
    (select 
         id, STD_ID, SUB_ID, 
         case ISNUMERIC(PR_MARCHAR) 
            when 1 then CAST(PR_MARCHAR as decimal) 
            else 0 
         end as PR_MARCHAR,
         case ISNUMERIC(TH_MARKS) 
            when 1 then CAST(TH_MARKS as decimal) 
            else 0 
         end as TH_MARKS ,
         case ISNUMERIC(ASG_MARK) 
            when 1 then CAST(ASG_MARK as decimal) 
            else 0 
         end as ASG_MARK
     from 
         MARKS_ENTRY  
     group by 
         id, STD_ID, SUB_ID, ASG_MARK, TH_MARKS, PR_MARCHAR) MARKS

Answer 1

Instead of fixing this query, I would suggest you to store NULL value instead of AB when the student is absent.

UPDATE Marks_Entry
SET    ASG_MARK = NULL
WHERE  ASG_MARK = 'AB'

UPDATE Marks_Entry
SET    TH_MARKS = NULL
WHERE  TH_MARKS = 'AB'

UPDATE Marks_Entry
SET    PR_MARCHAR = NULL
WHERE  PR_MARCHAR = 'AB' 

Alter the datatype of ASG_MARK, TH_MARKS and PR_MARCHAR columns

ALTER TABLE Marks_Entry
  ALTER COLUMN ASG_MARK DECIMAL(10, 2)

ALTER TABLE Marks_Entry
  ALTER COLUMN TH_MARKS DECIMAL(10, 2)

ALTER TABLE Marks_Entry
  ALTER COLUMN PR_MARCHAR DECIMAL(10, 2) 

Now the query can be written in much easier way

SELECT id,
       STD_ID,
       SUB_ID,
       ASG_MARK = COALESCE(Cast(ASG_MARK as varchar(100)),'AB'),
       TH_MARKS = COALESCE(Cast(TH_MARKS as varchar(100)),'AB'),
       PR_MARCHAR = COALESCE(Cast(PR_MARCHAR as varchar(100)),'AB'),
       TOTAL = CASE
                 WHEN ASG_MARK IS NULL AND TH_MARKS IS NULL AND PR_MARCHAR IS NULL THEN 'AB'
                 ELSE Cast(COALESCE(ASG_MARK, 0) + COALESCE(TH_MARKS, 0) + COALESCE(PR_MARCHAR, 0) AS VARCHAR(100))
               END,
       GRADE = CASE
                 WHEN orgTotal BETWEEN 91 AND 100 THEN 'A1'
                 WHEN orgTotal BETWEEN 81 AND 90 THEN 'B1'
                 WHEN orgTotal BETWEEN 71 AND 80 THEN 'C1'
                 WHEN orgTotal BETWEEN 61 AND 70 THEN 'D1'
                 WHEN orgTotal BETWEEN 51 AND 60 THEN 'E1'
                 WHEN orgTotal BETWEEN 41 AND 50 THEN 'F1'
                 WHEN orgTotal = 0 AND ABcheck <> 1 THEN '0'
                 ELSE 'AB'
               END
FROM   MARKS_ENTRY
       CROSS apply (SELECT COALESCE(ASG_MARK, 0)+COALESCE(TH_MARKS, 0)+COALESCE(PR_MARCHAR, 0),
                           CASE
                             WHEN ASG_MARK IS NULL AND TH_MARKS IS NULL AND PR_MARCHAR IS NULL THEN 1 ELSE 0
                           END) tc (orgTotal, ABcheck) 

• REXTESTER Demo