Stored Procedure to Update Data

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 to Update Data

Author

Topic

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 16:30:40

Based on the Rk1, Rk2, Rk3 fields I would like to populate the Order column:

rk1 rk2 rk3 name wins points diff Order
1 4 4 administrator 16 50 66 1
2 4 1 administrator2 15 50 0 2
3 1 3 administrator3 14 5001 6 3
3 2 2 administrator4 14 5000 5 4
3 2 4 administrator5 14 5000 66 5

For this scenerio it should be populated 1, 2, 3, 4, 5.
I have three ranks calculating based off wins desc, points desc, and diff. The order field will be blank and I need to run a stored procedure to update it, the ranking of this data based on the rk1, rk2, and rk3 fields.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 16:33:06

Add an IDENTITY columns to your resultset and insert the data from the query ordered by rk1, rk2, rk3.

E 12°55'05.25"
N 56°04'39.16"

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 16:35:06

sometimes a tie can happen it is not always sequential:

Rank
1 Tied
1 Tied
3
4
5 Tied
5 Tied
5 Tied
8

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 16:36:27

I am trying 8 million different ways to solve post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105291

I am so frustrated, been working the problem for 2 weeks...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 16:42:53

Do the clustered index update trick.
Many examples here.

Create table #stage (col1 int, col2 int, col3 int, ... , order int)

insert #stage
{your select query here}

create clustered index ix_stage on #stage (col1, col2, col3)

declare @col1 int, @col2 int, @col3 int, @order int

select top 1 @col1 = col1, @col2 = col2, @col3 = col3, @order = 1
from #stage
order by col1, col2, col3

update #stage
set @order = order = case when col1 = @col1 and col2 = @col2 and col3 = @col3 then @order else @order + 1 end,
@col1 = col1, @col2 = col2, @col3 = col3

select * from #Stage

E 12°55'05.25"
N 56°04'39.16"

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 16:56:37

I am not sure I follow clustered index update trick yet, I will study your examples.

Do you know what I am missing in my other post? Why does my sub query rank fail?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 16:58:34

quote:
Originally posted by JSQLTECH94

Do you know what I am missing in my other post? Why does my sub query rank fail?

Most likely duplicate rk1, rk2 and rk3.

E 12°55'05.25"
N 56°04'39.16"

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 17:03:29

no this query:

Here is my entire query:

SELECT

TOP 100 PERCENT

R1.AutoPoolID,
R1.PoolType,
R1.Week,
R1.Username,
R1.TieBreakerTotal,
R1.Wins,
R1.Losses,
R1.Ties,
R1.TBActual,
R1.TBDifference,
R1.WinPercentage,
R1.Points_Thursday,
R1.Points_Saturday,
R1.Points_Sunday,
R1.Points_Monday,
R1.Points_Total,

(
SELECT COUNT(*)
FROM tblWeeklyStandings_All R2
WHERE R2.AutoPoolID = R1.AutoPoolID AND R2.Week = R1.Week AND
(
R2.PSWins > R1.PSWins OR
R2.PSWins = R1.PSWins AND
R2.PSPoints_Total > R1.PSPoints_Total OR
R2.PSPoints_Total = R1.PSPoints_Total AND R2.PSTBDifference < R1.PSTBDifference
)
) + 1 AS Rank

FROM dbo.tblWeeklyStandings_All R1 INNER JOIN
dbo.qryUsers_SDR ON R1.AutoPoolID = dbo.qryUsers_SDR.AutoPoolID AND R1.PoolID = dbo.qryUsers_SDR.PoolID
WHERE
(R1.PoolType = '2007' OR R1.PoolType = '2008') AND
(dbo.qryUsers_SDR.OrderBy1 = '6') AND
(dbo.qryUsers_SDR.PointSpread = 1)

ORDER BY R1.PSWins DESC, R1.PSPoints_Total DESC, R1.PSTBDifference

Here are the results:
Rank Username Wins Points TB Difference
1. test 0618084 16 50 0
4. test 0618083 16 50 66
3. test 0618081 15 50 55
4. test 0618082 14 50 55
5. admin 2 0 0

As you can see the ranking is not correct. It should be the following:
Rank Username Wins Points TB Difference
1. test 0618084 16 50 0
2. test 0618083 16 50 66
3. test 0618081 15 50 55
4. test 0618082 14 50 55
5. admin 2 0 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 17:06:01

I don't have the will to debug your code without proper sample data.
Try my suggestion and tell me what you think.

E 12°55'05.25"
N 56°04'39.16"

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 17:10:57

Please, here is the sample data, pretty straight forward....I am really close

AutoPoolID PoolType PoolID Username PSWins PSPoints_Total PSTBDifference
2022 2007 256 administrator 2 0 0
2022 2007 9499 test 0618081 15 50 55
2022 2007 9500 test 0618082 14 50 55
2022 2007 9501 test 0618083 16 50 66
2022 2007 9502 test 0618084 16 50 0

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 17:13:18

just omit the R1. columns I left out. I provided the fields withs joins and that the ranking sub query works off.

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-24 : 15:33:00

peso any luck?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources