How to get value as string in SQL Server

Question

I have a table tbl_Marks:

Std_id  | sub_id  |  term_I   | term_2
--------+---------+-----------+-------
std_1   | 1       |   40      | 50
std_1   | 2       |   30      | 40
std_1   | 3       |   20      | 30
std_1   | 4       |   10      | 50

std_2   | 1       |   50      | 50
std_2   | 2       |   50      | 50
std_2   | 3       |   50      | 50
std_2   | 4       |   50      | 50

How can I get result like this:

Std_id  | sub_id  |  term_I   | term_2 | total  | status | PROMOTION_status
--------+---------+-----------+--------+--------+--------+------------------
std_1   | 1       |   40      | 50     | 90     | PASS   | REPEATER
std_1   | 2       |   30      | 40     | 70     | PASS   | REPEATER
std_1   | 3       |   20      | 20     | 40     | FAIL   | REPEATER
std_1   | 4       |   10      | 50     | 60     | PASS   | REPEATER

Note : if total value is less than 50 of any sub_id

std_2   | 1       |   50      | 50     | 100    | PASS   | PROMOTED
std_2   | 2       |   50      | 50     | 100    | PASS   | PROMOTED
std_2   | 3       |   50      | 50     | 100    | PASS   | PROMOTED
std_2   | 4       |   50      | 50     | 100    | PASS   | PROMOTED

Note: if total value is greater than 50 or equal of each sub_id

Please help!

Answer 1

Following SQL Select statement can help Instead of SQL CTE expression you can also use subselect statements But the main trick in this query is using MIN aggregation function with Partition by clause This helps you to check if there is a lower than 50 mark for that student, if so it makes all records for that student as REPEATER status

;with cte as (
select  
*, 
term_1 + term_2 as total
from tbl_Marks
)
select
*,
case when (term_1 + term_2) >= 50 then 'PASS' else 'FAIL' end as status,
case when (
min(total) over (partition by Std_id)
) >= 50 then 'PROMOTED' else 'REPEATER' end as PROMOTION_status
from cte

I hope it helps,

Answer 2

Use CASE.

 select t.*,
        t.term_I + t.term_2 as total,
        case when t.term_I + t.term_2 >= 50 then 'pass' else 'fail' end as status,
        case when t.std_id = 'std_2' then 'PRMOTED' else 'REAPEATER' end as promotion_status
 from tbl_marks t