PLS HELP ME FOR GENERATING THE RANK COLUMN FOR THE STUDENT TABLE.

Question

0.00/5 (No votes)

See more:

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.

SQL

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)))

SQL

--FUNCTION TO GENERATE TOTAL AS PER M1,M2,M3
CREATE FUNCTION dbo.total(@M1 INT,@M2 INT,@M3 INT)
RETURNS INT
AS
BEGIN
	RETURN(@M1+@M2+@M3)
END

SQL

--FUNCTION TO GENERATE THE AVERAGE AS PER M1,M2,M3
CREATE FUNCTION dbo.average(@M1 INT,@M2 INT,@M3 INT)
RETURNS FLOAT
AS
BEGIN
	RETURN((@M1+@M2+@M3)/3)
END

SQL

--FUNCTION TO GENERATE THE RESULT AS 'PASS' OR 'FAIL' IF HE SCORES LESS THEN 35 IN NY OF HIS SUBJECTS
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

SQL

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)

--here i created one more column for ClassRank
ALTER TABLE TTSTUDENT ADD ClassRank INT

//thanks in advance for ur kind help.....

Posted 19-Feb-12 17:29pm

The Doer

Updated 19-Feb-12 18:26pm

Michel [mjbohn]

Add a Solution

Comments

Johannes Hillert 20-Feb-12 1:24am

Hello, you will need a trigger for that (CREATE TRIGGER..), just look it up in the help files or on the net, it's pretty intuitive.. can you please not write the question title in all capitals next time, thank you.

E.F. Nijboer 20-Feb-12 3:27am

Are you sure you want to add the field to the table instead of creating a view for that? A view is ideal for calculated values.

The Doer 20-Feb-12 5:22am

view may be but i want separate column..

The Doer 20-Feb-12 5:25am

am trying hard to create that trigger but still undone :(

Nilesh Patil Kolhapur 20-Feb-12 5:59am

Hey wait my solution ready for ur requirement

The Doer 20-Feb-12 6:03am

yes Nilesh pls give...

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

The Doer · Accepted Answer · 2012-02-19T23:48:00

Solution 1

// I CREATED THE TRIGGER TO DO THIS..!

CREATE TRIGGER setRank 
ON TTSTUDENT
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
	create table #tt(rnk int identity(1,1),totl int)
	insert into #tt(totl) select tot from ttstudent order by tot desc
	declare rnkCursor CURSOR STATIC
	FOR SELECT rnk,totl from #tt
	DECLARE @rnk INT,@totl INT
	open rnkCursor
	FETCH NEXT FROM rnkCursor INTO @rnk,@totl
	while @@fetch_status<>-1
	BEGIN
		UPDATE TTSTUDENT SET ClassRank =@RNK WHERE TOT=@TOTL
		FETCH NEXT FROM rnkCursor INTO @rnk,@totl
	END
	DROP TABLE #TT
	DEALLOCATE rnkCursor
END

Posted 19-Feb-12 23:48pm

The Doer

Updated 20-Feb-12 19:45pm

v2

Comments

Nilesh Patil Kolhapur 20-Feb-12 7:08am

I think Your Trigger not work properly
if bot tot same then rank will not show properly check it out

The Doer 21-Feb-12 1:03am

yeah man i have checked...

it shows the same rank to all those who have same total..

1 22 50 50 122 40 F 3 9
2 10 60 40 110 36 F 3 10
5 21 31 32 84 28 F 3 11
7 10 78 84 172 57 F 3 3
10 70 80 90 240 80 P 3 2
11 70 80 99 249 83 P 1 1
81 70 15 50 135 45 F 3 8
82 70 35 55 160 53 P 3 7
83 70 35 55 160 53 P 3 7
84 70 35 55 160 53 P 3 7
85 70 35 55 160 53 P 3 7

Rajesh Anuhya 21-Feb-12 1:45am

Edited: "Treat my content as plain text, not as HTML" checkbox disabled.
--RA

Nilesh Patil Kolhapur · Accepted Answer · 2012-02-20T01:20:00

Hi Teju,

This is Perfect Stored Procedure for Your Requirement

---Calling of Sp
--exec spName 1,23,34,45,1
create procedure spName
(
   	@stdId  int=0,
	@m1 int=0,
	@m2 int=0,
	@m3 int=0,
	@Oper int=0  --pass 1 for new, 2 for update, 3 for Delete
)
as
begin 
declare @Result as char
declare @tot as int
declare @average as float
set @tot = @m1+@m2+@m3
set @average=@tot/3
set @Result='P'
if( @m1<35 or @m2<35 or @m3 <35) 
 begin
	set @Result='F'
 end
if(@Oper=1)
	insert into TTSTUDENT (stdId,m1,m2,m3,tot,average,Result) values(@stdId,@m1,@m2,@m3,@tot,@average,@Result)
else if(@Oper=2)
    update TTSTUDENT set m1=@m1,m2=@m2,m3=@m3,tot=@tot,average=@average,Result=@Result
    where stdId=@stdId
else if(@Oper=3)
    delete from TTSTUDENT where stdId=@stdId

----------------------- Cursor For Set Rank Run Tme--------------------
declare @intAvg as int
declare @rank as int
set @rank=1
DECLARE Curtemp Cursor LOCAL SCROLL STATIC  
			FOR 
			SELECT distinct average From TTSTUDENT order by average Desc
			OPEN Curtemp
			Fetch Next from Curtemp into @intAvg
			WHILE @@FETCH_STATUS=0  
			BEGIN		
				Begin		
					Update TTSTUDENT Set ClassRank=@rank Where average = @intAvg
				End
				IF @@error<>0 
				BEGIN
					CLOSE Curtemp
					DEALLOCATE Curtemp
					SET NOCOUNT OFF
					return
				END
				set @rank= @rank + 1
				Fetch Next from Curtemp into @intAvg
			END	--while
			CLOSE Curtemp
			DEALLOCATE Curtemp
end

It Works 100% accept this solution

Best Luck

The Doer · Accepted Answer · 2012-02-20T19:12:00

//this is one more alternative solution for the above problem without using cursors...

create TRIGGER setRank1
ON TTSTUDENT
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
	create table #tt(rnk int identity(1,1),totl int,stdId int)
	insert into #tt(totl,stdId) select tot,stdId from ttstudent order by tot desc
	update cc
	SET CC.ClassRank=T.RNK
	FROM TTSTUDENT CC,#TT T WHERE CC.TOT=T.TOTL 
	 
     update TTSTUDENT
     set ClassRank=null

	DROP TABLE #TT
	SELECT * FROM TTSTUDENT
END

PLS HELP ME FOR GENERATING THE RANK COLUMN FOR THE STUDENT TABLE.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0