Sql sever problem solve - 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

General SQL Server Forums

New to SQL Server Programming

Sql sever problem solve

Author

Topic

souravray79
Starting Member

11 Posts

Posted - 2007-08-20 : 07:35:19

studnet table...

fileds are...Id,name,math,physics,chemistry marks,percentage,grade

percentage & grade will aumated field

if percentage is more than 60 then the grade will be 'A'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 07:47:46

CASE WHEN Grade > 0.60 THEN 'A'
ELSE 'Fail'
END

E 12°55'05.25"
N 56°04'39.16"

souravray79
Starting Member

11 Posts

Posted - 2007-08-21 : 07:01:49

plzzz read this problem properly

table student

field

stu_id,stu_name,math_marks,phy_marks,che_marks,Aggregate,grade

marks & Aggregate must be calculated filed and dependened on math,phy,che marks....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 07:05:25

AGGREGATE = ISNULL(math_marks, 0) + ISNULL(phy_marks, 0) + ISNULL(che_marks, 0)
GRADE = CASE WHEN ISNULL(math_marks, 0) + ISNULL(phy_marks, 0) + ISNULL(che_marks, 0) > 0.6 THEN 'A' ELSE 'F' END

Please explain problem properly.
Also provide valid sample data and your expected output.

E 12°55'05.25"
N 56°04'39.16"

souravray79
Starting Member

11 Posts

Posted - 2007-08-21 : 07:11:09

table:

stu_name, math, phy, che, aggr, grade

aggr & grade will be automated field..

means...if i put integer in fileds math,phy,che
aggr field will aumatic calculate the marks percentage and if the
marks percentage is greater than 60 then in the field 'grade will auomatic updated to 'A'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 07:14:37

Percentage of what?
What if Aggr is less than 60%? What will Aggr show then?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 07:15:07

How hard is it to submit complete problem statement and proper sample data with expected output?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 07:21:34

[code]DECLARE @Students TABLE
(
Name SYSNAME,
Math INT,
Phy INT,
Che INT,
Aggr AS (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0,
Grade AS CASE WHEN (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0 >= 0.6 THEN 'A' ELSE 'F' END
)

select * from @students

insert @students (name, math) values ('peso', 99)

select * from @students

update @students set phy = 74
where name = 'peso'

select * from @students

update @students set che = 81
where name = 'peso'

select * from @students[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 07:26:33

PLEASE DO NOT CROSS POST!
What is the reason behind this behaviour?

You only need to post once and you will get a solution if you are polite and provide enough information to solve your dilemma.

E 12°55'05.25"
N 56°04'39.16"

souravray79
Starting Member

11 Posts

Posted - 2007-08-21 : 07:27:06

if aggr> 60
grade 'A'
if aggr>50
garde 'B'
if aggr>40
grade 'C'
else 'fail'

i want to get all datas in same table

souravray79
Starting Member

11 Posts

Posted - 2007-08-21 : 07:29:33

stu_name math_marks phy_marks che_marks arrg grade

if i insert math_marks =60
phy_marks=60
che_marks=60 ...then aggrate = 60 and garde must be 'A'

souravray79
Starting Member

11 Posts

Posted - 2007-08-21 : 07:31:03

i'm just trying to make u understnd this problem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 07:32:59

[code]DECLARE @Students TABLE
(
Name SYSNAME,
Math INT,
Phy INT,
Che INT,
Aggr AS (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0,
Grade AS CASE when (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0 >= 0.6 THEN 'A' when (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0 >= 0.5 then 'B' when (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0 >= 0.4 then 'C' ELSE 'Fail' END
)

insert @students (name, che) values ('peso', 88)

select * from @students

update @students set phy = 53

select * from @students

update @students set phy = 74

select * from @students

update @students set math = 99

select * from @students[/code]

E 12°55'05.25"
N 56°04'39.16"

souravray79
Starting Member

11 Posts

Posted - 2007-08-23 : 07:17:25

the program u have written make a temporary table...i want permanet table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 07:22:22

You have to understand that the DECLARE part and INSERT part is just for creating an environment comparable to your's, since i don't have your data.

The important part here is for you to understand the two calculated columns in the table!
You can add these two to your own existing table!

ALTER TABLE <YourTableNameHere>
ADD Aggr AS (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0

ALTER TABLE <YourTableNameHere>
ADD Grade AS CASE when (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0 >= 0.6 THEN 'A' when (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0 >= 0.5 then 'B' when (isnull(Math, 0) + isnull(Phy, 0) + isnull(Che, 0)) / 300.0 >= 0.4 then 'C' ELSE 'Fail' END

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources