Need some help on this one - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Need some help on this one

Author

Topic

Dwish1372
Starting Member

7 Posts

Posted - 2009-01-28 : 19:43:26

Hey guys.. I've been racking my brain for about a week on how to create a tournament payout system for my website which uses SQL as the backend. I figured it out in Excel and was hoping someone could help me convert this thing into an SQL query/stored procedure. Here's a link to the excel file on Google Docs which does the calculation. You can click file > export to save it to your PC (for some reason it doesn't show the formula on google toolbar).

http://spreadsheets.google.com/ccc?key=pTrQVPrdo_B1rK5yFfZO8cg&hl=en

Basically it will pay 1 in 5 entries into the tournmament. And it must be dynamic, which it is... so that it can adjust based on the number of entrants. So if 100 people enter it will pay 20 of them and the prize breakdown gradually gets less as you go down to 20th place. It works perfectly in the excel file, just need it in SQL.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 00:16:41

www.rentacoder.com?

E 12°55'05.63"
N 56°04'39.26"

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-29 : 11:00:36

And you are paying how much to the person that does your work for you?

Some days you're the dog, and some days you're the fire hydrant

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 11:16:39

You can make a donation to my PayPal account

DECLARE	@Players INT,
	@Fee INT

SELECT	@Players = 50,
	@Fee = 20

SELECT	Number AS [Column E],
	1 + @Players / 5 - Number AS [Column F],
	CAST((1.0E + @Players / 5.0E - Number) * @Players * @Fee / (@Players / 5.0E) / (1.0E + @Players / 5.0E) * 2.0E AS DECIMAL(18, 2)) AS [Column G]
FROM	master..spt_values
WHERE	Type = 'P'
	AND Number BETWEEN 1 AND @Players / 5

E 12°55'05.63"
N 56°04'39.26"

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-29 : 12:23:13

Wow Peso, you must be super bored

Some days you're the dog, and some days you're the fire hydrant

Dwish1372
Starting Member

7 Posts

Posted - 2009-01-29 : 16:29:37

Thanks I figured out the statements to get @players and @fee - see below. One problem though - If the number of players is less than 5 it doesn't output anything. I realize I'm supposed to pay 1 in 5 but... what do you think should happen in that scenario? Can it be modified to just give everything to the top player if there are only 4 or less players total?

DECLARE
@Players INT,
@Fee INT

SET @Players = (SELECT COUNT(UserName) FROM Handicap)
SET @Fee = (SELECT SUM(amt) FROM Handicap)

SELECT Number AS [Column E],
1 + @Players / 5 - Number AS [Column F],
CAST((1.0E + @Players / 5.0E - Number) * @Players * @Fee / (@Players / 5.0E) / (1.0E + @Players / 5.0E) * 2.0E AS DECIMAL(18, 2)) AS [Column G]
FROM master..spt_values
WHERE Type = 'P'
AND Number BETWEEN 1 AND @Players / 5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 02:40:20

This?

-- Initialize user supplied parameters
DECLARE	@Players INT,
	@Fee INT

SELECT	@Players = 50,
	@Fee = 20

-- Set up control variables
DECLARE	@Purse INT,
	@Winners INT,
	@Parts MONEY

SELECT	@Purse = @Players * @Fee,
	@Winners = CEILING(@Players / 5.0E),
	@Parts = FLOOR(@Winners * (1.0E + @Winners) / 2.0E)

-- Display the winning list
SELECT		Number AS [Column E],
		1 + @Winners - Number AS [Column F],
		(1 + @Winners - Number) * @Purse / @Parts AS [Column G]
FROM		master..spt_values
WHERE		Type = 'P'
		AND Number BETWEEN 1 AND @Winners
ORDER BY	Number

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources