SQL Query

Blackvault · 25 Sep 2012 at 22:17

I need a little help with my Stored Procedure. I would like to iterate through a set of columns from a result and take each value and look up a corresponding value and then add all those together. The only way I can see of doing it, is by using separate variables for each week position and each weeks scores plus separate select statements. Can anyone help me? This obviously isn't good for scalibility And yes it is for the Poker League.

Code:

 @pName varchar(50) = ''
AS
BEGIN
	

	Declare @p1 int = 0
	Declare @p2 int = 0
	Declare @s1 int = 0
	Declare @s2 int = 0
	Declare @pUserID int = 0
	Declare @pTotal int = 0


	set @pUserID = (Select UserID from PKR_USERS where UserName = @pName)

	set @p1 = (Select WEEK1POS From PKR_TOURNAMENT12)
	set @p2 = (Select WEEK2POS From PKR_TOURNAMENT12)

	set @s1  = (Select Points from PKR_SCORING where Position = @p1)
	set @s2  = (Select Points from PKR_SCORING where Position = @p2)

	set @pTotal = @s1 + @s2

	Update PKR_RESULTS set points = @pTotal Where UserID =  @pUserID
END

PKR_Tournament12 Table

Code:

[USERID][WEEK1POS][WEEK2POS]
1	1	1

PKR_Scoring Table

Code:

[Position][Points]
1	10
2	9

Thanks,

Blackvault

Blackvault · 26 Sep 2012 at 10:12

Anyone?

HungryHippos · 26 Sep 2012 at 10:38

It strikes me that your Tournament table is not well designed for querying like this.

Instead of using:

Code:

[USERID][WEEK1POS][WEEK2POS]
1	1	1

I would use:

Code:

[USERID][WEEKNUMBER][POSITION]
1	1	1
1	2	1

That should make it simpler to sum results for a user when you use Joins, also you are not relying on making a new column for each week.

Blackvault · 26 Sep 2012 at 11:16

Eulogy said:
It strikes me that your Tournament table is not well designed for querying like this.

Instead of using:

Code:

[USERID][WEEK1POS][WEEK2POS] 1 1 1

I would use:

Code:

[USERID][WEEKNUMBER][POSITION] 1 1 1 1 2 1

That should make it simpler to sum results for a user when you use Joins, also you are not relying on making a new column for each week.

I hadn't thought that the Tournament table being incorrect. I'll design it and see if that improves it for me.

Thanks

Blackvault

HungryHippos · 26 Sep 2012 at 11:36

If it's live data I would potentially play around with some temp tables, or make some test ones with different names so you don't break anything before making changes. If the data is just you playing around then less relevant of course.

Blackvault · 26 Sep 2012 at 11:38

Eulogy said:
If it's live data I would potentially play around with some temp tables, or make some test ones with different names so you don't break anything before making changes. If the data is just you playing around then less relevant of course.

Just developing the Poker system. Currently using a spreadsheet at the moment so fear not I'll not lose the scores

Blackvault

Blackvault · 27 Sep 2012 at 08:34

Eulogy - Designing the Tournament table helped me devise the query. Thank you for your help.

Regards,

Blackvault

HungryHippos · 27 Sep 2012 at 08:47

Excellent, glad I could help.