SQL-Score calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL-Score calculation

Author

Topic

chinlax
Starting Member

30 Posts

Posted - 2011-10-15 : 23:25:27

Hi All,

I have this task to do. I am not able to understand, what needs to be done and how to approach and solution for this .......
Can any one help me out
____________________________________________________
Module : Score
Purpose : Find the Score value for Each Questions

SELECT Code,SectionID,QnNo,Description,MaxScore,DisOrder,Active,Total FROM tbIrAsScoreQnMst
SELECT QnCode,AnsCode,Description,Rating,DisOrder,Active FROM tbIrAsScoreAnsMst WHERE QnCode = 46
SELECT ID,AsYear,RefNumber,QnCode,AnsCode,Score FROM tbIrAsScoreAnsDtls WHERE QnCode = 46

Score Calculation
~~~~~~~~~~~~~~~~~~~
Case 1) All Single Answer Questions (Only one Answer)
tbIrAsScoreAnsDtls.Score = IF tbIrAsScoreQnMst.Total = 0 then 0
ELSE IF tbIrAsScoreAnsMst.Rating <= tbIrAsScoreQnMst.Total THEN (tbIrAsScoreAnsMst.Rating / tbIrAsScoreQnMst.Total) * tbIrAsScoreQnMst.MaxScore
Update the tbIrAsScoreAnsMst.Score value for that question (tbIrAsScoreAnsDtls)
Example :
tbIrAsScoreAnsDtls.Rating = 3
tbIrAsScoreQnMst.Total = 6
tbIrAsScoreQnMst.MaxScore = 1
tbIrAsScoreAnsDtls.Score = (3/6) * 1 = > 0.5 -- Values should be rounded by 2

Case 2) All Multiple Options (More than one Answers)
tbIrAsScoreAnsDtls.Score = IF tbIrAsScoreQnMst.Total = 0 then 0
ELSE IF SUM(tbIrAsScoreAnsMst.Rating) <= tbIrAsScoreQnMst.Total IF THEN (SUM(tbIrAsScoreAnsMst.Rating) / tbIrAsScoreQnMst.Total) * tbIrAsScoreQnMst.MaxScore
ELSE tbIrAsScoreQnMst.MaxScore
Update the tbIrAsScoreAnsDtls.Score value for that Answer Which one having minimum Rating
Example :
SUM(tbIrAsScoreAnsDtls.Rating) = 12
tbIrAsScoreQnMst.Total = 6
tbIrAsScoreQnMst.MaxScore = 1
tbIrAsScoreAnsDtls.Score = 1 -- tbIrAsScoreQnMst.MaxScore

SUM(tbIrAsScoreAnsDtls.Rating) = 4
tbIrAsScoreQnMst.Total = 6
tbIrAsScoreQnMst.MaxScore = 1
tbIrAsScoreAnsDtls.Score

(4/6) * 1 = > 0.666666 = > 0.67 -- Values should be rounded by 2

Update this value in tbIrAsScoreAnsDtls
Eg:
Qn 1 -> Ans No 1 -> Rating ->2.5
Qn 1 -> Ans No 2 -> Rating ->1.5
Qn 1 -> Ans No 3 -> Rating ->5
Qn 1 -> Ans No 4 -> Rating ->3

So We need to Update the Score in Qn 1-> Ans No 2 (Bcos this answer only having Minimum Rating)

********************** Assignment for Score Calculation *********************************/

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-15 : 23:42:18

sorry, we don't do homework here. Please attempt the assignment yourself and if you have specific question after that, we will be happy to help.

KH
[spoiler]Time is always against us[/spoiler]

chinlax
Starting Member

30 Posts

Posted - 2011-10-15 : 23:55:21

ok, but i am not able to understand how to approach can u just tell me whats the question is all about. and what needs to be done ....plsease.
just guidelines then i will carry out how to proceed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 00:10:15

basically you need to join between the tables on common columns and then apply calculations to get results you want.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chinlax
Starting Member

30 Posts

Posted - 2011-10-16 : 07:16:48

Hi All,

For the case 1) i,e
SELECT Code,SectionID,QnNo,Description,MaxScore,DisOrder,Active,Total FROM tbIrAsScoreQnMst
SELECT QnCode,AnsCode,Description,Rating,DisOrder,Active FROM tbIrAsScoreAnsMst WHERE QnCode = 46
SELECT ID,AsYear,RefNumber,QnCode,AnsCode,Score FROM tbIrAsScoreAnsDtls WHERE QnCode = 46

Score Calculation
~~~~~~~~~~~~~~~~~~~
Case 1) All Single Answer Questions (Only one Answer)
tbIrAsScoreAnsDtls.Score = IF tbIrAsScoreQnMst.Total = 0 then 0
ELSE IF tbIrAsScoreAnsMst.Rating <= tbIrAsScoreQnMst.Total THEN (tbIrAsScoreAnsMst.Rating / tbIrAsScoreQnMst.Total) * tbIrAsScoreQnMst.MaxScore
Update the tbIrAsScoreAnsMst.Score value for that question (tbIrAsScoreAnsDtls)
Example :
tbIrAsScoreAnsDtls.Rating = 3
tbIrAsScoreQnMst.Total = 6
tbIrAsScoreQnMst.MaxScore = 1
tbIrAsScoreAnsDtls.Score = (3/6) * 1 = > 0.5 -- Values should be rounded by 2

I wrote the query like this

select c.score, a.total,a.maxscore, a=(b.rating/a.total)*a.maxscore from tbIrAsScoreQnMst a, tbIrAsScoreAnsMst b, tbIrAsScoreAnsDtls c where a.Code=b.qncode and b.qncode=c.qncode;

for this-Update the tbIrAsScoreAnsMst.Score value for that question (tbIrAsScoreAnsDtls), what should i do i am not understanding
Pls help me out.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 07:25:45

[code]
DECLARE @UPDATED_ANS_DETAILS table
(
Qcode varchar(100),
ACode varchar(100),
SCore Numeric(5,2)
)
UPDATE ad
SET ad.score= CASE WHEN q.Total = 0 then 0
WHEN a.Rating <= q.Total THEN (a.Rating / q.Total) * q.MaxScore
OUTPUT INSERTED.QCode,INSERTED.ACode,INSERTED.Score INTO @UPDATED_ANS_DETAILS
FROM tbIrAsScoreAnsDtls ad
INNER JOIN tbIrAsScoreQnMst q
ON q.Code = ad.QnCode
INNER JOIN tbIrAsScoreAnsMst a
ON a.QnCode = ad.QnCode
AND a.AnsCode = ad.AnsCode

UPDATE a
SET a.Score = uad.Score
FROM tbIrAsScoreAnsMst a
INNER JOIN @UPDATED_ANS_DETAILS uad
ON uad.QCode = a.QnCode
AND uad.Acode= a.AnCode
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources