While reading your question, i noticed that you might have done a typeo in your Grace Marks required columns

For Scenario 1: 001 11 40 38 (2)

but for

Scenario 2 : 002 11 40 38 (-) Which should be (2) as well.

Considering this assumption following is the solution for your problem.

Declare @StudentGraceMarkConfig Table

(

StudentUSNNo varchar(3),

Gracemarks int

)

;

Insert into @StudentGraceMarkConfig

select '001', 5union all

select '002', 4union all

select '003', 5union all

select '004', 4union all

select '005', 2

;

Declare @Transactions Table

(

StudentUSNNo varchar(3),

SubjectCode varchar(3),

MinPassingMarks int,

ObtainedMarks int,

RequiredGraceMarks int

)

Insert into @Transactions

--- Scenario 1

Select '001', '11', 40, 38, 2 union all

Select '001', '12', 40, 39, 1

;

Insert into @Transactions

--- Scenario 2

Select '002', '11', 40, 38, 2 union all ------ Corrected Value here

Select '002', '12', 40, 36, 4

;

Insert into @Transactions

--- Scenario 3

Select '003 ', '11', 40, 35, 5 union all ------ Corrected Value here

Select '003 ', '12', 40, 37, 3 union all

Select '003 ', '13', 40, 39, 1

;

---------- Show Total Records

SELECT * FROM @StudentGraceMarkConfig

;

SELECT * FROM @Transactions

;

---- Query

SELECT

t.StudentUSNNo, sum(MinPassingMarks - ObtainedMarks) AS tomarks, c.Gracemarks

FROM

@Transactions t

JOIN

@StudentGraceMarkConfig c on t.StudentUSNNo = c.StudentUSNNo

WHERE

t.ObtainedMarks < t.MinPassingMarks

GROUP BY

t.StudentUSNNo, c.Gracemarks

HAVING

sum(MinPassingMarks - ObtainedMarks) <= c.Gracemarks

hope it helps