March 16, 2011 at 5:22 am
Hi All,
I need to write an SQL query for the below requirement.
My table is Grades and it has the below structure.
create table grades(id int,deptID int,locid int,desig varchar(10),grade varchar(2))
ID is a primary key and the composite (DeptID, LocID and desig) is another candidate key.
insert into grades values (1,4,10,'Class A', 'A')
insert into grades values (2,4,10,'Class B', 'A')
insert into grades values (3,4,10,'Class B', 'A')
insert into grades values (4,4,10,'OverAll', 'A')
So as the above insert scripts, "OverAll" can have a value explicitly in the table or else it could be null as given in the below scripts.
insert into grades values (5,5,10,'Class A', 'A')
insert into grades values (6,5,10,'Class B', 'A')
insert into grades values (6,5,10,'Class B', 'A')
insert into grades values (6,5,10,'OverAll', null)
Now, I have to write a query to update the grade column of grades table with the below rule. The is I need to update the null grade with the grade of Class A or B or C.
if Grade is Not Null
return Grade
Else
update Grade with the grade of class A,B and C...The grade of class A, B and C would always be the same.
Right now I have done this using a cursor and loops which looks quite ugly.
If anyone could guide me with a straight forward query that would be much appreciated.
Regards,
Jeet
March 16, 2011 at 5:29 am
Jeet
If Grade is NULL, how do you decide whether to update it with A, B or C?
John
March 16, 2011 at 6:03 am
If grade is null we can update either with A, B or C as these three values would always be same. (Please don't look at how logical it could be , but that's how the rule). I don't mind if we select either of these.
March 16, 2011 at 7:55 am
This is such a strange requirement that I think I must have misunderstood it. However, assuming I didn't, this will work:
create table #grades(id int,deptID int,locid int,desig varchar(10),grade varchar(2))
insert into #grades values (1,4,10,'Class A', 'A')
insert into #grades values (2,4,10,'Class B', 'A')
insert into #grades values (3,4,10,'Class B', 'A')
insert into #grades values (4,4,10,'OverAll', 'A')
insert into #grades values (5,5,10,'Class A', 'A')
insert into #grades values (6,5,10,'Class B', 'A')
insert into #grades values (6,5,10,'Class B', 'A')
insert into #grades values (6,5,10,'OverAll', null)
UPDATE #grades
SET grade = COALESCE(grade,'B') -- B chosen arbitrarily (could have been A or C instead)
John
March 16, 2011 at 8:59 am
Hi John,
Thanks and that should work. It did not occur to me to use coalesce after reading through on the odd requirement.
There could be a case if classB also null then we should to choose the other items but I can write that part.
Thanks again
jisth
March 16, 2011 at 9:02 am
I am posting my first question here! Don't we have a Mark as Answer option as like msdn forums? !
March 16, 2011 at 9:06 am
We do not have that. We have talked about it, but it's a double edged sword. what you get to work, and think is a good answer may not be a good answer.
March 16, 2011 at 9:20 am
Hi Steve, You made me look into the answer and I think I made some mistake in my original post which is why that become confusing.
create table #grades(id int,deptID int,locid int,desig varchar(10),grade varchar(2))
insert into #grades values (1,4,10,'Class A', 'A')
insert into #grades values (2,4,10,'Class B', 'A')
insert into #grades values (3,4,10,'Class c', 'A')
insert into #grades values (4,4,10,'OverAll', 'A')
insert into #grades values (5,5,10,'Class A', 'B')
insert into #grades values (6,5,10,'Class B', 'B')
insert into #grades values (6,5,10,''Class c', 'B')
insert into #grades values (6,5,10,'OverAll', null)
Now if "OverAll" is null I need to update with the grade of either ClassA, ClassB or ClassC of the same goup of deptID,locID.
So in the first set OverAll should be A if it was Null. In the second case Overall would be B.
Sorry for the confusing post and please take it as because of my inexperience!
Appreciate a help again.
Thanks,
Jith
March 16, 2011 at 9:32 am
Jith
What would happen if the same group of deptID, locID had different grades in it? For example, what if you had the following data?
insert into #grades values (5,5,10,'Class A', 'A')
insert into #grades values (6,5,10,'Class B', 'B')
insert into #grades values (6,5,10,'Class c', 'C')
insert into #grades values (6,5,10,'OverAll', null)
John
March 16, 2011 at 9:39 am
Hi John,
That's what I was trying to say my post taht it would never happen like that.
In each combination of ID,DeptID,LocID,Desig the Grade would be the same. That's why I will be Ok if I get any of the grades of ClassA, classB or classC.
Also ID is an identity so in the above example I have given ID 6 in more than one place and please read it as 7, 8 and so on.
Regards,
Jith
March 18, 2011 at 5:34 am
Jith
Is it possible that any particular combination of deptID and locid would contain all NULLs in the grade column? If so, what would happen then?
John
March 18, 2011 at 5:55 am
In that case overall would also be null since there is no other valid value.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply