this is my table and i want to generate the CLASS RANK as per TOT(TOTAL NUMBER),
the one with maximum number should have classRank as 1,next one as 2 and so on.
AS soon i insert,delete,update any value my CLASSRANK should update as the updated/inserted/deleted value.
//thanks in advance for ur kind help.....
stdId M1 M2 M3 TOT AVERAGE RESULT CLASSRANK
1 22 50 50 122 40 F NULL
2 90 60 40 190 63 P NULL
3 35 50 52 137 45 P NULL
4 56 85 45 186 62 P NULL
5 21 31 32 84 28 F NULL
This is how i created the table.
CREATE TABLE ttStudent(stdId INT PRIMARY KEY,m1 INT,m2 INT,m3 INT,tot AS(dbo.total(m1,m2,m3)),average AS (dbo.average(m1,m2,m3)),result AS (dbo.result(m1,m2,m3)))
CREATE FUNCTION dbo.total(@M1 INT,@M2 INT,@M3 INT)
RETURNS INT
AS
BEGIN
RETURN(@M1+@M2+@M3)
END
CREATE FUNCTION dbo.average(@M1 INT,@M2 INT,@M3 INT)
RETURNS FLOAT
AS
BEGIN
RETURN((@M1+@M2+@M3)/3)
END
ALTER FUNCTION DBO.result(@M1 INT,@M2 INT,@M3 INT)
RETURNS CHAR(1)
AS
BEGIN
IF( @M1<35 OR @M2<35 OR @M3<35)
BEGIN
RETURN 'F'
END
ELSE
BEGIN
RETURN 'P'
END
RETURN ''
END
SELECT * FROM ttSTUDENT
INSERT INTO ttSTUDENT VALUES(1,22,50,50)
INSERT INTO ttSTUDENT VALUES(2,90,60,40)
INSERT INTO ttSTUDENT VALUES(3,35,50,52)
INSERT INTO ttSTUDENT VALUES(4,56,85,45)
INSERT INTO ttSTUDENT VALUES(5,21,31,32)
ALTER TABLE TTSTUDENT ADD ClassRank INT
//thanks in advance for ur kind help.....