June 24, 2016 at 3:18 am
Need assistance for below scenario in SQL server 2005.
=========================================
I currently have two columns in the master table i.e
Student USN No Grace marks (Eligible)
========== ================
001 5
002 4
003 5
004 4
005 2
In the above mentioned columns students with the USN no's are eligible for the grace marks.
Now the scenarios :
===============
Scenario 1 (Transaction table ):
Student USN No Subject Code Min Passing marks Obtained marks Grace Marks required
=====================================================================================
001 11 40 38 (2)
001 12 40 39 (1)
Since student "001" is eligible for 5 grace marks as mentioned in the above master table, he will be made as PASS.
Scenario 2 (Transaction table ):
Student USN No Subject Code Min Passing marks Obtained marks Grace Marks required
=====================================================================================
002 11 40 38 (-)
002 12 40 36 (4)
Since student "002" is eligible for 4 grace marks as mentioned in the above master table, he will be made as PASS in one subject only.
Scenario 3 (Transaction table ) " Issue scenario":
==============
Student USN No Subject Code Min Passing marks Obtained marks Grace Marks required
=====================================================================================
003 11 40 35 (-)
003 12 40 37 (3)
003 13 40 39 (1)
" In the above USN '003' Case,This student i want to pass two subjects
In this above scnario . i want to apply grace marks
June 24, 2016 at 3:57 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply