How to handle CASE related query??
-
select Student, course, credit, sum(gradeno) as Grades, case
when sum(gradeno) BETWEEN 91 and 100 then 'A+'
when sum(gradeno) BETWEEN 83 and 90 then 'A'
when sum(gradeno) BETWEEN 75 and 82 then 'B+'
when sum(gradeno) BETWEEN 67 and 74 then 'B'
when sum(gradeno) BETWEEN 59 and 66 then 'C+'
when sum(gradeno) BETWEEN 51 and 58 then 'A'
when sum(gradeno) BETWEEN 43 and 50 then 'A'
when sum(gradeno) BETWEEN 35 and 42 then 'A'
when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then 'ABS'
when sum(gradeno) BETWEEN 0 and 34 then 'F'
else 'NULL'
end 'Grade'
, case
when sum(gradeno) BETWEEN 91 and 100 then '8'
when sum(gradeno) BETWEEN 83 and 90 then '7'
when sum(gradeno) BETWEEN 75 and 82 then '6'
when sum(gradeno) BETWEEN 67 and 74 then '5'
when sum(gradeno) BETWEEN 59 and 66 then '4'
when sum(gradeno) BETWEEN 51 and 58 then '3'
when sum(gradeno) BETWEEN 43 and 50 then '2'
when sum(gradeno) BETWEEN 35 and 42 then '1'
when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
when sum(gradeno) BETWEEN 0 and 34 then '0'
else '0'
end 'Points'
, case
when sum(gradeno) BETWEEN 91 and 100 then 8 * credit
when sum(gradeno) BETWEEN 83 and 90 then 7 * credit
when sum(gradeno) BETWEEN 75 and 82 then 6 * credit
when sum(gradeno) BETWEEN 67 and 74 then 5 * credit
when sum(gradeno) BETWEEN 59 and 66 then 4 * credit
when sum(gradeno) BETWEEN 51 and 58 then 3 * credit
when sum(gradeno) BETWEEN 43 and 50 then 2 * credit
when sum(gradeno) BETWEEN 35 and 42 then 1 * credit
when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
when sum(gradeno) BETWEEN 0 and 34 then '0'
else '0'
end 'Quality'
from MyTable
group by Student, course, credit
Check this code, it's giving no error!!
Yuppie.....:D
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
Sir, one more favour!
I want this whenever I update data in MyTable, then through this T-SQL, the Student, course,credit,grades,grade,points,quality will automatically updates in one table!
Is this possible or not through some storedprocedure or function??
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
Mail2Payan
I am not sure I understand your question properly. All the information you want to display is accessible by using your T-SQL code and there is no need for a secondl table, just create your "MyTable", using an appropriate name, in your database and then:
What I would suggest is that you create a series of stored procedures.
A). To add new students
B). To add new data for existing students.
C). To update an entry previously made to correct an error, if any, in the original input data
D). Create a stored procedure using the current T-SQL that gives you the result you want to display
E). Modify the procedure created in step (D) to display the results for a individual student by adding a WHERE clause using a specific studentId passed to the procedure using a parameter.
When you want a user to view the results for all students invoke the procedure created in step (D). To view a specific student invoke the stored procedure created in step (E)
F). Do not forget that eventually you will may want to delete or archive information for students who have graduated.
-
Image Link: https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
This is my whole database schema, I have those stored procedures which you mentioned and thanks for your guidance.
Sir in my last T-SQL, I want another extra stuff, i.e. I want Total Quality/Total Credit, e.g.
---------- ---------- ------ ----------- ----- ------ -----------
Student course credit Grades Grade Points Quality
---------- ---------- ------ ----------- ----- ------ -----------
Andy ASM01 3 60 C+ 4 12
Andy OB01 3 70 B 5 15
Bob ASM01 3 100 A+ 8 24
Bob OB01 3 82 B+ 6 18
Steve ASM01 3 93 A+ 8 24
---------- ---------- ------ ----------- ----- ------ -----------
15 93
---------- ---------- ------ ----------- ----- ------ -----------
(6 row(s) affected)
So, GPA = Total Quality/Total Credit = 93/15 = 6.20
Hope, I make you understand!!
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
[font="Verdana"]
I suggest you move your grade calculations into a table, rather than in code (that's what databases are for!)
Then you can have a calculation similar to the following:
if object_id('dbo.StudentGrade') is not null
drop table dbo.StudentGrade;
create table dbo.StudentGrade(
StudentGradeID smallint identity not null,
Student varchar(10) not null,
Course varchar(10) not null,
Credit tinyint not null,
GradeNo tinyint not null,
Remark varchar(10) null,
constraint PK_StudentGrade primary key(
StudentGradeID
)
);
insert into dbo.StudentGrade(
Student,
Course,
Credit,
GradeNo,
Remark
)
select 'Steve', 'ASM01', 3, 50, null union all
select 'Steve', 'ASM01', 3, 43, null union all
select 'Bob', 'ASM01', 3, 0, null union all
select 'Bob', 'OB01', 3, 23, null union all
select 'Bob', 'OB01', 3, 59, null union all
select 'Bob', 'ASM01', 3, 100, null union all
select 'Andy', 'OB01', 3, 0, null union all
select 'Andy', 'ASM01', 3, 50, null union all
select 'Andy', 'ASM01', 3, 10, null union all
select 'Andy', 'OB01', 3, 70, null union all
select 'Andy', 'OB01', 3, 0, 'ABS';
if object_id('dbo.GradeRate') is not null
drop table dbo.GradeRate;
create table dbo.GradeRate(
GradeRateID tinyint identity not null,
LowGradeNo tinyint not null,
HighGradeNo tinyint not null,
Grade varchar(2) not null,
Points tinyint not null,
constraint PK_GradeRate primary key(
GradeRateID
),
constraint AK_GradeRate_GradeRange unique(
LowGradeNo,
HighGradeNo
),
constraint AK_GradeRate_Points unique(
Points
)
);
insert into dbo.GradeRate(
LowGradeNo,
HighGradeNo,
Grade,
Points
)
select 0, 34, 'F', 0 union all
select 35, 42, 'A', 1 union all -- is this really an A? probably an E
select 43, 50, 'A', 2 union all -- is this really an A? probably a D
select 51, 58, 'A', 3 union all -- is this really an A? probably a C
select 59, 66, 'C+', 4 union all
select 67, 74, 'B', 5 union all
select 75, 82, 'B+', 6 union all
select 83, 90, 'A', 7 union all
select 91, 100, 'A+', 8;
with
OverallStudentGrade as (
select Student,
Course,
Credit,
case Remark when 'ABS' then 1 else 0 end as IsAbsent,
(sum(GradeNo) / count(1)) as GradeNo
from dbo.StudentGrade
group by
Student,
Course,
Credit,
case Remark when 'ABS' then 1 else 0 end
),
CalculatedStudentGrade as (
select osg.Student,
osg.Course,
osg.Credit,
osg.IsAbsent,
osg.GradeNo,
case osg.IsAbsent when 1 then 'ABS' else isnull(gr.Grade, 'X') end as Grade,
case osg.IsAbsent when 1 then '0' else cast(isnull(gr.Points, 0) as varchar) end as Points,
case osg.IsAbsent when 1 then 0 else isnull(gr.Points, 0) end * osg.Credit as Quality
from OverallStudentGrade osg
left outer join
dbo.GradeRate gr
on osg.GradeNo between gr.LowGradeNo and gr.HighGradeNo
)
select Student,
Course,
Credit,
IsAbsent,
GradeNo,
Grade,
Points,
Quality
from CalculatedStudentGrade
order by
Student,
Course
compute sum(Credit),
sum(Quality);
[/font]
-
Thanks for your effort.
Yes Sir, the triple A should be C, D+ and D, that was my typing mistake, sorry!
Am getting error while running this part of T-SQL:
withOverallStudentGrade as (
select Student,
Course,
Credit,
case Remark when 'ABS' then 1 else 0 end as IsAbsent,
(sum(GradeNo) / count(1)) as GradeNo
from dbo.StudentGrade
group by
Student,
Course,
Credit,
case Remark when 'ABS' then 1 else 0 end
),
CalculatedStudentGrade as (
select osg.Student,
osg.Course,
osg.Credit,
osg.IsAbsent,
osg.GradeNo,
case osg.IsAbsent when 1 then 'ABS' else isnull(gr.Grade, 'X') end as Grade,
case osg.IsAbsent when 1 then '0' else cast(isnull(gr.Points, 0) as varchar) end as Points,
case osg.IsAbsent when 1 then 0 else isnull(gr.Points, 0) end * osg.Credit as Quality
from OverallStudentGrade osg
left outer join
dbo.GradeRate gr
on osg.GradeNo between gr.LowGradeNo and gr.HighGradeNo
)
select Student,
Course,
Credit,
IsAbsent,
GradeNo,
Grade,
Points,
Quality
from CalculatedStudentGrade
order by
Student,
Course
compute sum(Credit),
sum(Quality);
How should I execute this part of query cause whenever am running with this T-SQL, am getting the following error:
Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'with'.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near ','.
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
[font="Verdana"]If you are using SQL Server 2000, you will need to convert the CTEs (common table expressions -- the parts in the "with" statement) to derived tables.
select Student,
Course,
Credit,
IsAbsent,
GradeNo,
Grade,
Points,
Quality
from (
select osg.Student,
osg.Course,
osg.Credit,
osg.IsAbsent,
osg.GradeNo,
case osg.IsAbsent when 1 then 'ABS' else isnull(gr.Grade, 'X') end as Grade,
case osg.IsAbsent when 1 then '0' else cast(isnull(gr.Points, 0) as varchar) end as Points,
case osg.IsAbsent when 1 then 0 else isnull(gr.Points, 0) end * osg.Credit as Quality
from (
select Student,
Course,
Credit,
case Remark when 'ABS' then 1 else 0 end as IsAbsent,
(sum(GradeNo) / count(1)) as GradeNo
from dbo.StudentGrade
group by
Student,
Course,
Credit,
case Remark when 'ABS' then 1 else 0 end
) osg
left outer join
dbo.GradeRate gr
on osg.GradeNo between gr.LowGradeNo and gr.HighGradeNo
) CalculatedStudentGrade
order by
Student,
Course
compute sum(Credit),
sum(Quality);
[/font]
-
Yes Sir, am using MS SQL Server 2000 and am StucK.
Am getting the Answer perfectly, but with the old problem i.e.
-----------------------------------------------------------------------------------------------
StudentCourseCreditIsAbsentGradeNoGradePointsQuality
-----------------------------------------------------------------------------------------------
AndyASM013030F00
AndyOB013035D13
AndyOB01310ABS00
BobASM013050D+26
BobOB013041D13
SteveASM013046D+26
-----------------------------------------------------------------------------------------------
See this Andy part, appearing twice, where it should appear as:
-----------------------------------------------------------------------------------------------
StudentCourseCreditIsAbsentGradeNoGradePointsQuality
-----------------------------------------------------------------------------------------------
AndyOB013035D13
-----------------------------------------------------------------------------------------------
And I don't need the IsAbsent column.
What if I have ABS as well as DN (Denial of Grade)??
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
[font="Verdana"]I've posted the code changes above. :)[/font]
-
Am getting the Answer perfectly, but with the old problem i.e.
-----------------------------------------------------------------------------------------------
Student Course Credit IsAbsent GradeNo Grade Points Quality
-----------------------------------------------------------------------------------------------
Andy ASM01 3 0 30 F 0 0
Andy OB01 3 0 35 D 1 3
Andy OB01 3 1 0 ABS 0 0
Bob ASM01 3 0 50 D+ 2 6
Bob OB01 3 0 41 D 1 3
Steve ASM01 3 0 46 D+ 2 6
-----------------------------------------------------------------------------------------------
See the Andy - OB01 part, appearing twice, where it should appear as:
-----------------------------------------------------------------------------------------------
Student Course Credit IsAbsent GradeNo Grade Points Quality
-----------------------------------------------------------------------------------------------
Andy OB01 3 0 35 D 1 3
-----------------------------------------------------------------------------------------------
What if I have ABS as well as DN (Denial of Grade)??
I modified little bit of your T-SQL:
select Student,Course,
Credit,
IsAbsent,
GradeNo,
Grade,
Points,
Quality,
Quality/Credit as Aggregate
from (
select osg.Student,
osg.Course,
osg.Credit,
osg.IsAbsent,
osg.GradeNo,
case osg.IsAbsent when 1 then 'ABS' else isnull(gr.Grade, 'X') end as Grade,
case osg.IsAbsent when 1 then '0' else cast(isnull(gr.Points, 0) as varchar) end as Points,
case osg.IsAbsent when 1 then 0 else isnull(gr.Points, 0) end * osg.Credit as Quality
from (
select Student,
Course,
Credit,
case Remark when 'ABS' then 1 else 0 end as IsAbsent,
(sum(GradeNo) / count(1)) as GradeNo
from dbo.StudentGrade
group by
Student,
Course,
Credit,
case Remark when 'ABS' then 1 else 0 end
) osg
left outer join
dbo.GradeRate gr
on osg.GradeNo between gr.LowGradeNo and gr.HighGradeNo
) CalculatedStudentGrade
order by
Student,
Course
compute sum(Credit),
sum(Quality);
I want to compute sum(Aggregate), but can't do it, please help me out!!
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
[font="Verdana"]The clues are there... what have you tried?[/font]
-
Sir,
Am trying!
I am taking reference of my code:
when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'and trying to fit in your code:
..
case osg.IsAbsent when 1 then 'ABS' else isnull(gr.Grade, 'X') end as Grade,
.
.
case Remark when 'ABS' then 1 else 0 end as IsAbsent,
.
.
But getting error,please sir!
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
---------------------------------------- -
[font="Verdana"]I wouldn't try mixing your code with mine. Just use mine.
As a hint, you could take the entire select statement and make it another derived table...
I think you have enough of the code now that with some playing around yourself and reading up on books online, you should be able to make the alterations. Remember, you are here to learn!
Good luck. 😀
[/font]
-
I tried but failed, I read http://www.java2s.com, http://www.w3schools.com, but not getting the clue,am very new to this MS SQL, please help me...:crying:
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply