Question IF...THEN...ELSE statement within SQL UPDATE SELECT

S

Simply Me

Member
Joined
Nov 3, 2012
Messages
17
Programming Experience
Beginner
Hi!


I would like to compute for the general average of a student who belong to a certain class section. The flow of query will look like
VB.NET: 
IF SectionName='Section1' THEN
   update a table field GenAve using a formula
ELSEIF SectionName="Section2" THEN
   update a table field GenAve using another formula
ElSE
   update a table field GenAve using another formula
END IF
Is this possible?

I was trying to do it like this
VB.NET: 
If SectionName = 'Section1'
	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(PE * 1.2))/7.2 
	SELECT DISTINCT IDNO,LastName,FirstName,MI,GENDER,
		YEARLEVEL,SECTIONNAME,Adviser,[English],[Math],[Speech], 
		[PE],[Science],[Earth Science]
	FROM tblGradeK12
ELSE
if SectionName = 'Section2'
	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(PE * 1.2) +
									([Earth Science] * 1))/8.2 
	SELECT DISTINCT IDNO,LastName,FirstName,MI,GENDER,
		YEARLEVEL,SECTIONNAME,Adviser,[English],[Math],[Speech], 
		[PE],[Science],[Earth Science]
	FROM tblGradeK12
 
Sort by date Sort by votes
Hi,

You need to use the Case statement to do this sort of thing within SQL. The structure of the Case statement is as follows:-

VB.NET: 
CASE <FieldName> WHEN '<Value1>' THEN <Expression1> WHEN <Value2> THEN <Expression2> WHEN <Value3> THEN <Expression3> WHEN <Value4> THEN <Expression4> ELSE <Expression5> END
OR:-

VB.NET: 
CASE WHEN <FieldName>='<Value1>' THEN <Expression1> WHEN <FieldName>=<Value2> THEN <Expression2> WHEN <etc> ELSE <DefaultExpression> END
Hope that helps.

Cheers,

Ian
 
Upvote 0 Downvote
Hi!
Thanks for your quick reply...

Actually this is what I have in my .rdlc General Ave column and I want to convert it into SQL so that I will just use the fieldname instead of this very long code.
VB.NET: 
=FormatNumber(Switch(Fields!YearLevel.Value="LC7" or Fields!YearLevel.Value="LC8",(IIF(Fields!SectionName.Value<>"FirstYearSection1" and 
Fields!SectionName.Value<>"SecondYearSection1",
((Fields!Speech.Value * 1.2) + 
(Fields!English.Value * 1.5) + 
(Fields!Math.Value * 1.5) + 
(Fields!Science.Value * 1.8) + 
(Fields!Literature.Value * 1.2) + 
(Round((Fields!TLE.Value * .50) + (Fields!Computer.Value * .50),0) * 1.2) + 
(Round((Fields!Music.Value * .25) + (Fields!Arts.Value * .25) + (Fields!PE.Value * .25) + (Fields!Health.Value * .25),0) * 1.2) + 
(Fields!CLE.Value * 0.9))/10.5,


((Fields!Speech.Value * 1.2) + 
(Fields!English.Value * 1.5) + 
(Fields!Math.Value * 1.5) + 
(Fields!Science.Value * 1.8) + 
(Fields!Literature.Value * 1.2) + 
(Round((Fields!TLE.Value * .50) + (Fields!Computer.Value * .50),0) * 1.2) + 
(Round((Fields!Music.Value * .25) + (Fields!Arts.Value * .25) + (Fields!PE.Value * .25) + (Fields!Health.Value * .25),0) * 1.2) +  
(Fields!CLE.Value * 0.9) +  
(Fields!Earth_Science.Value * 1) + 
(Fields!Geometry.Value * 1))/11.5   )),


Fields!YearLevel.Value="LC9",
(IIF(Fields!SectionName.Value<>"ThirdyearSection1" , 
((Fields!Speech.Value * 1.2) + 
(Fields!English.Value * 1.5) + 
(Fields!Math.Value * 1.5) + 
(Fields!Science.Value * 1.8) + 
(Fields!Literature.Value * 1.2) + 
(Round((Fields!TLE.Value * .50) + (Fields!Computer.Value * .50),0) * 1.2) +
(Round((Fields!Music.Value * .25) + (Fields!Arts.Value * .25) + (Fields!PE.Value * .25) + (Fields!Health.Value * .25),0) * 1.2) + 
(Fields!CLE.Value * 0.9))/10.5,


((Fields!Speech.Value * 1.2) + 
(Fields!English.Value * 1.5) + 
(Fields!Math.Value * 1.5) + 
(Fields!Science.Value * 1.8) + 
(Fields!Literature.Value * 1.2) + 
(Round((Fields!TLE.Value * .50) + (Fields!Computer.Value * .50),0) * 1.2) + 
(Round((Fields!Music.Value * .25) + (Fields!Arts.Value * .25) + (Fields!PE.Value * .25) + (Fields!Health.Value * .25),0) * 1.2) +  
(Fields!CLE.Value * 0.9) +  
(Fields!Research1.Value * 1) + 
(Fields!Adv_Biology.Value * 1)) /12.5)),


Fields!YearLevel.Value="LC10",
(IIf(Fields!SectionName.Value<>"FourthYearSiction1",
((Fields!Speech.Value * 1.2) + 
(Fields!English.Value * 1.5) + 
(Fields!Math.Value * 1.5) + 
(Fields!Science.Value * 1.8) + 
(Fields!Literature.Value * 1.2) + 
(Round((Fields!TLE.Value * .50) + (Fields!Computer.Value * .50),0) * 1.2) + 
(Round((Fields!Music.Value * .25) + (Fields!Arts.Value * .25) + (Fields!PE.Value * .25) + (Fields!Health.Value * .25),0) * 1.2) +  + 
(Fields!CLE.Value * 0.9) + 
(Fields!CAT.Value * 0.3))/10.8,


((Fields!Speech.Value * 1.2) + 
(Fields!English.Value * 1.5) + 
(Fields!Math.Value * 1.5) + 
(Fields!Science.Value * 1.8) + 
(Fields!Literature.Value * 1.2) + 
(Round((Fields!TLE.Value * .50) + (Fields!Computer.Value * .50),0) * 1.2) + 
(Round((Fields!Music.Value * .25) + (Fields!Arts.Value * .25) + (Fields!PE.Value * .25) + (Fields!Health.Value * .25),0) * 1.2) +  
(Fields!CLE.Value * 0.9) + 
(Fields!CAT.Value * 0.3) + 
(Fields!Adv_Chemistry.Value * 1) +
(Fields!Research2.Value * 1))/12.8))),3)
 
Upvote 0 Downvote
Hi!

I did something like this
VB.NET: 
CASE WHEN Yearlevel='LC7' and SectionName='FirstYearSection1' THEN 
 	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(Literature * 1.2) +
									(TLEComputer * 1.2) +
									(MAPEH * 1.2) +
									(CLE * 0.9) +
									([Earth Science] * 1))/11.5
WHEN Yearlevel='LC8' and SectionName='SecondYearSection1' THEN 
 	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(Literature * 1.2) +
									(TLEComputer * 1.2) +
									(MAPEH * 1.2) +
									(CLE * 0.9) +
									(Geometry * 1))/11.5 
WHEN Yearlevel='LC9' and SectionName='ThirdYearSection1' THEN
 	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(Literature * 1.2) +
									(TLEComputer * 1.2) +
									(MAPEH * 1.2) +
									(CLE * 0.9) +
									([Research1 * 1) +
									([Adv Biology] *1))/12.5
WHEN Yearlevel='LC10' and SectionName='FourthYearSection1' THEN
 	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(Literature * 1.2) +
									(TLEComputer * 1.2) +
									(MAPEH * 1.2) +
									(CLE * 0.9) +
									(Research2 * 1) +
									([Adv Chemistry] * 1) +
									(CAT * 0.3)/12.8
WHEN Yearlevel='LC10' and SectionName<>'FourtYearSection1' THEN
 	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(Literature * 1.2) +
									(TLEComputer * 1.2) +
									(MAPEH * 1.2) +
									(CLE * 0.9) +
									(CAT * 0.3))/10.8
ELSE  	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(Literature * 1.2) +
									(TLEComputer * 1.2) +
									(MAPEH * 1.2) +
									(CLE * 0.9))/10.5
END
But I get syntax error. What am i missing?
VB.NET: 
Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 35
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 57
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Line 65
Incorrect syntax near 'END'
 
Upvote 0 Downvote
Hi!

I managed to find the errors, but I have some issues:
1. Only the "FirstYearSection1" GenAve is computed.
2. It seems that the ELSE portion is not executed because the rest of the sections general average are not computed at all. I see Null values.
3. The computed General Average is displayed in another column (Column No Name) instead of in GenAve column.
4. How do I make the result to display only 2 decimal places?

Here's what I managed to do. Any suggestions is highly appreaciated.
VB.NET: 
select IDNO,LastName, FirstName, MI,YearLevel,SectionName,GenAve,CASE
WHEN SectionName='FirstYearSection1' THEN 
                                     Round((((Speech * 1.2) + 
                                    (English * 1.5) + 
                                    (Math * 1.5) + 
                                    (Science * 1.8) + 
                                    (Literature * 1.2) +
                                    (TLEComputer * 1.2) +
                                    (MAPEH * 1.2) +
                                    (CLE * 0.9) +
                                    ([Earth Science] * 1))/11.5),3)
WHEN SectionName='SecondYearSesction1' THEN 
                                     Round((((Speech * 1.2) + 
                                    (English * 1.5) + 
                                    (Math * 1.5) + 
                                    (Science * 1.8) + 
                                    (Literature * 1.2) +
                                    (TLEComputer * 1.2) +
                                    (MAPEH * 1.2) +
                                    (CLE * 0.9) +
                                    (Geometry * 1))/11.5),3) 
WHEN SectionName='ThirdYearSection1' THEN
                                     Round((((Speech * 1.2) + 
                                    (English * 1.5) + 
                                    (Math * 1.5) + 
                                    (Science * 1.8) + 
                                    (Literature * 1.2) +
                                    (TLEComputer * 1.2) +
                                    (MAPEH * 1.2) +
                                    (CLE * 0.9) +
                                    (Research1 * 1) +
                                    ([Adv Biology] *1))/12.5),3)
WHEN SectionName='FourthYearSection1' THEN
                                     Round((((Speech * 1.2) + 
                                    (English * 1.5) + 
                                    (Math * 1.5) + 
                                    (Science * 1.8) + 
                                    (Literature * 1.2) +
                                    (TLEComputer * 1.2) +
                                    (MAPEH * 1.2) +
                                    (CLE * 0.9) +
                                    (Research2 * 1) +
                                    ([Adv Chemistry] * 1) +
                                    (CAT * 0.3))/12.8),3)
WHEN SectionName <> 'FourthYearSection1' THEN
                                     Round((((Speech * 1.2) + 
                                    (English * 1.5) + 
                                    (Math * 1.5) + 
                                    (Science * 1.8) + 
                                    (Literature * 1.2) +
                                    (TLEComputer * 1.2) +
                                    (MAPEH * 1.2) +
                                    (CLE * 0.9) +
                                    (CAT * 0.3))/10.8),3)
ELSE 
                                    Round((((Speech * 1.2) + 
                                    (English * 1.5) + 
                                    (Math * 1.5) + 
                                    (Science * 1.8) + 
                                    (Literature * 1.2) +
                                    (TLEComputer * 1.2) +
                                    (MAPEH * 1.2) +
                                    (CLE * 0.9))/10.5),3)
END
FROM TBLGRADEK12
 
Last edited:
Upvote 0 Downvote
Just an update...I managed to have it working but still the results are in another column instead of displaying it in the GenAve column and also the results look like 76.257000, I want it to be 76.257.

Any idea pls?
 
Upvote 0 Downvote
Hi,

Each additional column is an SQL query is separated with a comma as you have got. Each column can then be a field or any expression that you have defined. In your example you have created the column GenAve but then your have created an addional unnamed column with your calculated expression. To combine the two you need to remove your current GenAve column and then modify your expression to be:-

CASE WHEN etc, THEN etc, etc END as GenAve

To then return a specific number of decimal places and truncate the trailing zeros you need to Cast the result to the correct type. i.e:-

CAST(ROUND(123.4567891,3) as Decimal(18,3))

Hope that helps.

Cheers,

Ian
 
Upvote 0 Downvote
Hi!

Thank you again for your reply. I was able to have it working before I saw your post. I did not do it the way you mentioned in your post. I am posting my code here for any comments or suggestions to make it more better or efficient code.
IanRyder said:
Hi,

Each additional column is an SQL query is separated with a comma as you have got. Each column can then be a field or any expression that you have defined. In your example you have created the column GenAve but then your have created an addional unnamed column with your calculated expression. To combine the two you need to remove your current GenAve column and then modify your expression to be:-

CASE WHEN etc, THEN etc, etc END as GenAve

To then return a specific number of decimal places and truncate the trailing zeros you need to Cast the result to the correct type. i.e:-

CAST(ROUND(123.4567891,3) as Decimal(18,3))

Hope that helps.

Cheers,

Ian
Click to expand...

Here's what I have:
VB.NET: 
Update tblgradek12Set GenAve =
--select IDNO,LastName, FirstName, MI,YearLevel,SectionName,GenAve,
(
CASE
WHEN YearLevel='LC7' and SectionName='FirstYearSection1' THEN 
 	Round((((ISNULL(Speech,0) * 1.2) + (ISNULL(English,0) * 1.5) + (ISNULL(Math,0) * 1.5) + (ISNULL(Science,0) * 1.8) + (ISNULL([Literature],0) * 1.2) +
		(ISNULL(TLEComputer,0) * 1.2) + (ISNULL(MAPEH,0) * 1.2) + (ISNULL(CLE,0) * 0.9) + (ISNULL([Earth Science],0) * 1))/11.5),3)


WHEN YearLevel='LC8' and SectionName='SecondYearSection1' THEN 
 	Round((((ISNULL(Speech,0) * 1.2) + (ISNULL(English,0) * 1.5) + (ISNULL(Math,0) * 1.5) + (ISNULL(Science,0) * 1.8) + (ISNULL([Literature],0) * 1.2) +
		(ISNULL(TLEComputer,0) * 1.2) + (ISNULL(MAPEH,0) * 1.2) + (ISNULL(CLE,0) * 0.9) + (ISNULL(Geometry,0) * 1))/11.5),3) 


WHEN YearLevel='LC9' and SectionName='ThirdYearSection1' THEN
	Round((((ISNULL(Speech,0) * 1.2) + (ISNULL(English,0) * 1.5) + (ISNULL(Math,0) * 1.5) + (ISNULL(Science,0) * 1.8) + (ISNULL([Literature],0) * 1.2) +
		(ISNULL(TLEComputer,0) * 1.2) + (ISNULL(MAPEH,0) * 1.2) + (ISNULL(CLE,0) * 0.9) + (ISNULL(Research1,0) * 1) + (ISNULL([Adv Biology],0) *1))/12.5),3)


WHEN YearLevel='LC10' and SectionName='FourthYearSection1' THEN
 	Round((((ISNULL(Speech,0) * 1.2) +(ISNULL(English,0) * 1.5) + (ISNULL(Math,0) * 1.5) + (ISNULL(Science,0) * 1.8) + (ISNULL([Literature],0) * 1.2) + 
		(ISNULL(TLEComputer,0) * 1.2) + (ISNULL(MAPEH,0) * 1.2) + (ISNULL(CLE,0) * 0.9) + (ISNULL(Research2,0) * 1) + (ISNULL([Adv Chemistry],0) * 1) + 
		(ISNULL(CAT3,0) * 0.3))/12.8),3)


WHEN YearLevel='LC10' and SectionName <> 'FourthYearSection1' THEN
 	Round((((ISNULL(Speech,0) * 1.2) + (ISNULL(English,0) * 1.5) + (ISNULL(Math,0) * 1.5) + (ISNULL(Science,0) * 1.8) + (ISNULL([Literature],0) * 1.2) + 
		(ISNULL(TLEComputer,0) * 1.2) + (ISNULL(MAPEH,0) * 1.2) + (ISNULL(CLE,0) * 0.9) + (ISNULL(CAT,0) * 0.3))/10.8),3)


ELSE 
	Round((((ISNULL(Speech,0) * 1.2) + (ISNULL(English,0) * 1.5) + (ISNULL(Math,0) * 1.5) + (ISNULL(Science,0) * 1.8) + (ISNULL([Literature],0) * 1.2) + 
		(ISNULL(TLEComputer,0) * 1.2) + (ISNULL(MAPEH,0) * 1.2) + (ISNULL(CLE,0) * 0.9))/10.5),3)
END
)
FROM TBLGRADEK12


--Second Grading
Update tblgradek12
Set GenAve2 =
--select IDNO,LastName, FirstName, MI,YearLevel,SectionName,GenAve,
(
CASE
WHEN YearLevel='LC7' and SectionName='FirstYearSection1' THEN 
 	Round((((ISNULL(Speech2,0) * 1.2) + (ISNULL(English2,0) * 1.5) + (ISNULL(Math2,0) * 1.5) + (ISNULL(Science2,0) * 1.8) + (ISNULL([Literature2],0) * 1.2) +
		(ISNULL(TLEComputer2,0) * 1.2) + (ISNULL(MAPEH2,0) * 1.2) + (ISNULL(CLE2,0) * 0.9) + (ISNULL([Earth Science2],0) * 1))/11.5),3)


WHEN YearLevel='LC8' and SectionName='SecondYearSection1' THEN 
 	Round((((ISNULL(Speech2,0) * 1.2) + (ISNULL(English2,0) * 1.5) + (ISNULL(Math2,0) * 1.5) + (ISNULL(Science2,0) * 1.8) + (ISNULL([Literature2],0) * 1.2) +
		(ISNULL(TLEComputer2,0) * 1.2) + (ISNULL(MAPEH2,0) * 1.2) + (ISNULL(CLE2,0) * 0.9) + (ISNULL(Geometry2,0) * 1))/11.5),3) 


WHEN YearLevel='LC9' and SectionName='ThirdYearSection1' THEN
	Round((((ISNULL(Speech2,0) * 1.2) + (ISNULL(English2,0) * 1.5) + (ISNULL(Math2,0) * 1.5) + (ISNULL(Science2,0) * 1.8) + (ISNULL([Literature2],0) * 1.2) +
		(ISNULL(TLEComputer2,0) * 1.2) + (ISNULL(MAPEH2,0) * 1.2) + (ISNULL(CLE2,0) * 0.9) + (ISNULL(Research1_2,0) * 1) + (ISNULL([Adv Biology2],0) *1))/12.5),3)


WHEN YearLevel='LC10' and SectionName='FourthYearSection1' THEN
 	Round((((ISNULL(Speech2,0) * 1.2) +(ISNULL(English2,0) * 1.5) + (ISNULL(Math2,0) * 1.5) + (ISNULL(Science2,0) * 1.8) + (ISNULL([Literature2],0) * 1.2) + 
		(ISNULL(TLEComputer2,0) * 1.2) + (ISNULL(MAPEH2,0) * 1.2) + (ISNULL(CLE2,0) * 0.9) + (ISNULL(Research2_2,0) * 1) + (ISNULL([Adv Chemistry2],0) * 1) + 
		(ISNULL(CAT3,0) * 0.3))/12.8),3)


WHEN YearLevel='LC10' and SectionName <> 'FourthYearSection1' THEN
 	Round((((ISNULL(Speech2,0) * 1.2) + (ISNULL(English2,0) * 1.5) + (ISNULL(Math2,0) * 1.5) + (ISNULL(Science2,0) * 1.8) + (ISNULL([Literature2],0) * 1.2) + 
		(ISNULL(TLEComputer2,0) * 1.2) + (ISNULL(MAPEH2,0) * 1.2) + (ISNULL(CLE2,0) * 0.9) + (ISNULL(CAT2,0) * 0.3))/10.8),3)


ELSE 
	Round((((ISNULL(Speech2,0) * 1.2) + (ISNULL(English2,0) * 1.5) + (ISNULL(Math2,0) * 1.5) + (ISNULL(Science2,0) * 1.8) + (ISNULL([Literature2],0) * 1.2) + 
		(ISNULL(TLEComputer2,0) * 1.2) + (ISNULL(MAPEH2,0) * 1.2) + (ISNULL(CLE2,0) * 0.9))/10.5),3)
END
)
FROM TBLGRADEK12 




--Third Grading
Update tblgradek12
Set GenAve3 =
--select IDNO,LastName, FirstName, MI,YearLevel,SectionName,GenAve,
(
CASE
WHEN YearLevel='LC7' and SectionName='FirstYearSection1' THEN 
 	Round((((ISNULL(Speech3,0) * 1.2) + (ISNULL(English3,0) * 1.5) + (ISNULL(Math3,0) * 1.5) + (ISNULL(Science3,0) * 1.8) + (ISNULL([Literature3],0) * 1.2) +
		(ISNULL(TLEComputer,0) * 1.2) + (ISNULL(MAPEH3,0) * 1.2) + (ISNULL(CLE3,0) * 0.9) + (ISNULL([Earth Science3],0) * 1))/11.5),3)


WHEN YearLevel='LC8' and SectionName='SecondYearSection1' THEN 
 	Round((((ISNULL(Speech3,0) * 1.2) + (ISNULL(English3,0) * 1.5) + (ISNULL(Math3,0) * 1.5) + (ISNULL(Science3,0) * 1.8) + (ISNULL([Literature3],0) * 1.2) +
		(ISNULL(TLEComputer3,0) * 1.2) + (ISNULL(MAPEH3,0) * 1.2) + (ISNULL(CLE3,0) * 0.9) + (ISNULL(Geometry3,0) * 1))/11.5),3) 


WHEN YearLevel='LC9' and SectionName='ThirdYearSection1' THEN
	Round((((ISNULL(Speech3,0) * 1.2) + (ISNULL(English3,0) * 1.5) + (ISNULL(Math3,0) * 1.5) + (ISNULL(Science3,0) * 1.8) + (ISNULL([Literature3],0) * 1.2) +
		(ISNULL(TLEComputer3,0) * 1.2) + (ISNULL(MAPEH3,0) * 1.2) + (ISNULL(CLE3,0) * 0.9) + (ISNULL(Research1_3,0) * 1) + (ISNULL([Adv Biology3],0) *1))/12.5),3)


WHEN YearLevel='LC10' and SectionName='FourthYearSection1' THEN
 	Round((((ISNULL(Speech3,0) * 1.2) +(ISNULL(English3,0) * 1.5) + (ISNULL(Math3,0) * 1.5) + (ISNULL(Science3,0) * 1.8) + (ISNULL([Literature3],0) * 1.2) + 
		(ISNULL(TLEComputer3,0) * 1.2) + (ISNULL(MAPEH3,0) * 1.2) + (ISNULL(CLE3,0) * 0.9) + (ISNULL(Research2_3,0) * 1) + (ISNULL([Adv Chemistry3],0) * 1) + 
		(ISNULL(CAT3,0) * 0.3))/12.8),3)


WHEN YearLevel='LC10' and SectionName <> 'FourthYearSection1' THEN
 	Round((((ISNULL(Speech3,0) * 1.2) + (ISNULL(English3,0) * 1.5) + (ISNULL(Math3,0) * 1.5) + (ISNULL(Science3,0) * 1.8) + (ISNULL([Literature3],0) * 1.2) + 
		(ISNULL(TLEComputer3,0) * 1.2) + (ISNULL(MAPEH3,0) * 1.2) + (ISNULL(CLE3,0) * 0.9) + (ISNULL(CAT3,0) * 0.3))/10.8),3)


ELSE 
	Round((((ISNULL(Speech3,0) * 1.2) + (ISNULL(English3,0) * 1.5) + (ISNULL(Math3,0) * 1.5) + (ISNULL(Science3,0) * 1.8) + (ISNULL([Literature3],0) * 1.2) + 
		(ISNULL(TLEComputer3,0) * 1.2) + (ISNULL(MAPEH3,0) * 1.2) + (ISNULL(CLE3,0) * 0.9))/10.5),3)
END
)
FROM TBLGRADEK12 




--Fourth Grading
Update tblgradek12
Set GenAve4 =
--select IDNO,LastName, FirstName, MI,YearLevel,SectionName,GenAve,
(
CASE
WHEN YearLevel='LC7' and SectionName='FirstYearSection1' THEN 
 	Round((((ISNULL(Speech4,0) * 1.2) + (ISNULL(English4,0) * 1.5) + (ISNULL(Math4,0) * 1.5) + (ISNULL(Science4,0) * 1.8) + (ISNULL([Literature4],0) * 1.2) +
		(ISNULL(TLEComputer4,0) * 1.2) + (ISNULL(MAPEH4,0) * 1.2) + (ISNULL(CLE4,0) * 0.9) + (ISNULL([Earth Science4],0) * 1))/11.5),3)


WHEN YearLevel='LC8' and SectionName='SecondYearSection1' THEN 
 	Round((((ISNULL(Speech4,0) * 1.2) + (ISNULL(English4,0) * 1.5) + (ISNULL(Math4,0) * 1.5) + (ISNULL(Science4,0) * 1.8) + (ISNULL([Literature4],0) * 1.2) +
		(ISNULL(TLEComputer4,0) * 1.2) + (ISNULL(MAPEH4,0) * 1.2) + (ISNULL(CLE4,0) * 0.9) + (ISNULL(Geometry4,0) * 1))/11.5),3) 


WHEN YearLevel='LC9' and SectionName='ThirdYearSection1' THEN
	Round((((ISNULL(Speech4,0) * 1.2) + (ISNULL(English4,0) * 1.5) + (ISNULL(Math4,0) * 1.5) + (ISNULL(Science4,0) * 1.8) + (ISNULL([Literature4],0) * 1.2) +
		(ISNULL(TLEComputer4,0) * 1.2) + (ISNULL(MAPEH4,0) * 1.2) + (ISNULL(CLE4,0) * 0.9) + (ISNULL(Research1_4,0) * 1) + (ISNULL([Adv Biology4],0) *1))/12.5),3)


WHEN YearLevel='LC10' and SectionName='FourthYearSection1' THEN
 	Round((((ISNULL(Speech4,0) * 1.2) +(ISNULL(English4,0) * 1.5) + (ISNULL(Math4,0) * 1.5) + (ISNULL(Science4,0) * 1.8) + (ISNULL([Literature4],0) * 1.2) + 
		(ISNULL(TLEComputer4,0) * 1.2) + (ISNULL(MAPEH4,0) * 1.2) + (ISNULL(CLE4,0) * 0.9) + (ISNULL(Research2_4,0) * 1) + (ISNULL([Adv Chemistry4],0) * 1) + 
		(ISNULL(CAT3,0) * 0.3))/12.8),3)


WHEN YearLevel='LC10' and SectionName <> 'FourthYearSection1' THEN
 	Round((((ISNULL(Speech4,0) * 1.2) + (ISNULL(English4,0) * 1.5) + (ISNULL(Math4,0) * 1.5) + (ISNULL(Science4,0) * 1.8) + (ISNULL([Literature4],0) * 1.2) + 
		(ISNULL(TLEComputer4,0) * 1.2) + (ISNULL(MAPEH4,0) * 1.2) + (ISNULL(CLE4,0) * 0.9) + (ISNULL(CAT4,0) * 0.3))/10.8),3)


ELSE 
	Round((((ISNULL(Speech4,0) * 1.2) + (ISNULL(English4,0) * 1.5) + (ISNULL(Math4,0) * 1.5) + (ISNULL(Science4,0) * 1.8) + (ISNULL([Literature4],0) * 1.2) + 
		(ISNULL(TLEComputer4,0) * 1.2) + (ISNULL(MAPEH4,0) * 1.2) + (ISNULL(CLE4,0) * 0.9))/10.5),3)
END
)
FROM TBLGRADEK12
 
Upvote 0 Downvote
Hi,

In principal what you are trying to do looks fine if I am correct in thinking that you are updating MULTIPLE GenAve fields in your SQL table. This being GenAve, GenAve2, GenAve3 etc from multiple subject fields, being Math, Math2, Math3 etc.

That said, I am not sure this is what you are trying to do based on the fact that each group of coding is based around a different Grading. I guess it sounds more like you need to use some form of WHERE clause to select the Grading you are working with but this all depends on how you have set up your database structure???

Perhaps you need to explain in more detail your use of the MULIPLE GenAve fields and the MULTIPLE source field names, i.e:- Math, Math2, Math3 etc.

The only syntax error that I can see is the "FROM TBLGRADEK12" at the end of each of your Update statements. This is incorrect syntax for this type of statement.

Hope that helps.

Cheers,

Ian
 
Upvote 0 Downvote
Hi,

I have just looked at this a bit further and to be honest it does not make sense? Lets take two of the grades in two of your update statements and compare them, being:-

VB.NET: 
Update tblgradek12 Set GenAve =
--select IDNO,LastName, FirstName, MI,YearLevel,SectionName,GenAve,
(
CASE
WHEN YearLevel='LC7' and SectionName='FirstYearSection1' THEN 
      Round((((ISNULL(Speech,0) * 1.2) + (ISNULL(English,0) * 1.5) + (ISNULL(Math,0) * 1.5) + (ISNULL(Science,0) * 1.8) + (ISNULL([Literature],0) * 1.2) +
            (ISNULL(TLEComputer,0) * 1.2) + (ISNULL(MAPEH,0) * 1.2) + (ISNULL(CLE,0) * 0.9) + (ISNULL([Earth Science],0) * 1))/11.5),3)
 
etc
AND

VB.NET: 
Update tblgradek12
Set GenAve2 =
--select IDNO,LastName, FirstName, MI,YearLevel,SectionName,GenAve,
(
CASE
WHEN YearLevel='LC7' and SectionName='FirstYearSection1' THEN 
      Round((((ISNULL(Speech2,0) * 1.2) + (ISNULL(English2,0) * 1.5) + (ISNULL(Math2,0) * 1.5) + (ISNULL(Science2,0) * 1.8) + (ISNULL([Literature2],0) * 1.2) +
            (ISNULL(TLEComputer2,0) * 1.2) + (ISNULL(MAPEH2,0) * 1.2) + (ISNULL(CLE2,0) * 0.9) + (ISNULL([Earth Science2],0) * 1))/11.5),3)
 
etc
The expressions are exactly the same and the only difference is that you are updating a different field?

I think you definitely need to explain in more detail your use of MULTIPLE field names in what you are trying to do.

Cheers,

Ian
 
Upvote 0 Downvote
IanRyder said:
Hi,

In principal what you are trying to do looks fine if I am correct in thinking that you are updating MULTIPLE GenAve fields in your SQL table. This being GenAve, GenAve2, GenAve3 etc from multiple subject fields, being Math, Math2, Math3 etc.

That said, I am not sure this is what you are trying to do based on the fact that each group of coding is based around a different Grading. I guess it sounds more like you need to use some form of WHERE clause to select the Grading you are working with but this all depends on how you have set up your database structure???

Perhaps you need to explain in more detail your use of the MULIPLE GenAve fields and the MULTIPLE source field names, i.e:- Math, Math2, Math3 etc.

The only syntax error that I can see is the "FROM TBLGRADEK12" at the end of each of your Update statements. This is incorrect syntax for this type of statement.

Hope that helps.

Cheers,

Ian
Click to expand...
Hi!

Sorry for my late reply. To answer your questions:
1. I used multiple GenAve to get the General Average every grading period from first to 4th grading that's why I have in my SPROC GenAve, GenAve2, GenAve3 and GenAve4.
2. I used multiple Fieldnames like Math,Math2,Math3 and so on in my SPROC again to store the grades of students by grading period. In one of my table though, I just used Math. To give you and idea, in my other table, I have let say, StudName,SubjectName,Quizzes, Quiz%,Assign,Assign%,Recitation,Rec%, Exam,Exam% and Final Grade.To get the percentages, Quiz is multiplied to 20%, Assign to 10%, Recitation to 10% and Exam to 60%
Example:
John Doe Math 90 18 80 8 90 9 85 51 86

The 86 which is the Final Grade is the one being stored in GenAve in tblgradek12.
 
Upvote 0 Downvote
IanRyder said:
Hi Simply Me,

Ok that makes a bit more sense. The only thing I need to ask now is, Are you all sorted with what you need to solve your issues?

Cheers,

Ian
Click to expand...
Yes, its already working for me...I just need to be clarified with this against what I did...I just put [GenAve] [numeric](5, 3) NULL, in my SPROC.
VB.NET: 
[COLOR=#333333]CAST(ROUND(123.4567891,3) as Decimal(18,3))[/COLOR]
 
Last edited:
Upvote 0 Downvote

Similar threads

S
  • Question
Question Help on SQL Update
Replies
5
Views
2K
IanRyder
I

Share this page

Latest posts

Back
Top