Stored Procedure help needed.....

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

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure help needed.....

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2003-05-21 : 09:08:14

I'm using SQL7

The code below works fine but the score column shows a number representing the persons position. It only ever shows four rows i.e. a score of 4 is actually 1st place, 3 is 2nd place, 2 is 3rd place and 1 is fourth place.

So a typical resultset would be:


Joe Bloggs      ABN           4
Ivor Bigun      Deutsche      3
John Doe        UBS           2
Paul Knookie    IIRG          1

ok, hopefully that's clear !?
What i want to do is show '1st' instead of the 4, '2nd' instead of the 3, '3rd' instead of the 2 and obviously '4th' instead of the 1.

Is there a way of doing this within the SQL Statement below or do i have to do this within the front-end coding that EXECs the SP.

In MSAccess i know i could've used the IIF function for this, is there a MSSQL equivalent ?

any help appreciated
thanks

 
CREATE PROC usp_PlcVotedBemco_sel
@PlcCode int,
@UID varchar(7)
AS
SELECT 
	Broker_Employee.fs_forename, 
    	Broker_Employee.fs_surname,
	Brokers_in_UID.fs_brokernameinUID, 
    	Plc_Bemco_Ranking.fd_score
FROM 
	Broker_Employees_in_UID 
INNER JOIN
    	Broker_Employee ON 
    	Broker_Employees_in_UID.f_brokerEmployeeCode = Broker_Employee.fa_brokerEmployeeCode
INNER JOIN
    	Plc_Qaire ON 
    	Broker_Employees_in_UID.fs_UID = Plc_Qaire.fs_UID 
INNER JOIN
    	Plc_Bemco_Ranking ON 
    	Plc_Qaire.fa_qID = Plc_Bemco_Ranking.fi_plcQaireID AND 
    	Broker_Employee.fa_brokerEmployeeCode = Plc_Bemco_Ranking.fi_bemcoCode
INNER JOIN
    	Brokers_in_UID ON 
    	Broker_Employees_in_UID.fs_UID = Brokers_in_UID.fs_UID AND
     	Broker_Employees_in_UID.fl_brokerCode = Brokers_in_UID.fl_brokerCode
WHERE 
	Plc_Qaire.fi_plcCode = @PlcCode
AND 
    	Plc_Qaire.fs_UID = @UID
ORDER BY 
	Plc_Bemco_Ranking.fd_score DESC

====
Paul

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-21 : 09:39:19

Change this bit

quote:

SELECT
Broker_Employee.fs_forename,
Broker_Employee.fs_surname,
Brokers_in_UID.fs_brokernameinUID,
Plc_Bemco_Ranking.fd_score

to

SELECT
Broker_Employee.fs_forename,
Broker_Employee.fs_surname,
Brokers_in_UID.fs_brokernameinUID,
case
Plc_Bemco_Ranking.fd_score
when 1 then '4th'
when 2 then '3rd'
when 3 then '2nd'
when 4 then '1st'
end as ranking

-------
Moo.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-21 : 19:12:09

you could also do

(5-Plc_Bemco_Ranking.fd_score) As Ranking

You could make the 5 a variable so that you can pass in the number of positions your ranking.

Edited by - ValterBorges on 05/21/2003 19:16:17

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources