A better way? - 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 2000 Forums

Transact-SQL (2000)

A better way?

Author

Topic

Antonio
Posting Yak Master

168 Posts

Posted - 2006-06-02 : 07:51:25

Hi there,

This questions is probably for people who understand a little bit about betting. If I place a double on 4 selections, that is, I place a double on

A, B, C, D

What I am effectively doing is betting on the outcomes of :

A vs B
A vs C
A vs D

B vs A
B vs C
B vs D

C vs A
C vs B
C vs D

D vs A
D vs B
D vs C

You can see that the occurrence of a particular selection is 3 times. That is, A occurs three time as Avs B is in effect the same as B vs A.

I devised the following code to tell me the number of occurrences of a particular selection based on the type of bet and the number of selections but I get an arithmetic overflow when I try an 11 fold bet with 15 selections.

My code looks like :


DECLARE @BET int
DECLARE @SEls int
SELECT @bet = 2
SELECT @sels = 5


select CASE WHEN @BET = 2 
THEN (@sels-1)*1
WHEN @BET = 3
THEN (@sels-1)*(@sels-2)/2
WHEN @BET = 4
THEN (@sels-1)*(@sels-2)*(@sels-3)/(2*3)
WHEN @BET = 5
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)/(2*3*4)
WHEN @BET = 6
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)/(2*3*4*5)
WHEN @BET = 7
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)/(2*3*4*5*6)
WHEN @BET = 8
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)*(@sels-7)/(2*3*4*5*6*7)
WHEN @BET = 9
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)*(@sels-7)*(@sels-8)/(2*3*4*5*6*7*8)
WHEN @BET = 10
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)*(@sels-7)*(@sels-8)*(@sels-9)/(2*3*4*5*6*7*8*9)
WHEN @BET = 11
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)*(@sels-7)*(@sels-8)*(@sels-9)*(@sels-10)/(2*3*4*5*6*7*8*9*10)
WHEN @BET = 12
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)*(@sels-7)*(@sels-8)*(@sels-9)*(@sels-10)*(@sels-11)/(2*3*4*5*6*7*8*9*10*11)
WHEN @BET = 13
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)*(@sels-7)*(@sels-8)*(@sels-9)*(@sels-10)*(@sels-11)*(@sels-12)/(2*3*4*5*6*7*8*9*10*11*12)
WHEN @BET = 14
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)*(@sels-7)*(@sels-8)*(@sels-9)*(@sels-10)*(@sels-11)*(@sels-12)*(@sels-13)/(2*3*4*5*6*7*8*9*10*11*12*13)
WHEN @BET = 15
THEN (@sels-1)*(@sels-2)*(@sels-3)*(@sels-4)*(@sels-5)*(@sels-6)*(@sels-7)*(@sels-8)*(@sels-9)*(@sels-10)*(@sels-11)*(@sels-12)*(@sels-13)*(@sels-14)/(2*3*4*5*6*7*8*9*10*11*12*13*14)

END AS no_stakes

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 08:00:56

I don't think I fully understand the problem, but I have a couple of suggestions...

1. Use bigint instead of int (this prevents the 11-15 overflow)
2. Simplify the case statement using some mathematical functions (I can't suggest what since I don't fully understand - but it doesn't 'look' like that would be too difficult)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 08:01:46

Isn't
A versus B
B versus A

2 times only for the occurence of A related any other option?

Can someone please explain for me this occurence?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 08:13:26

quote:
Originally posted by RyanRandall

I don't think I fully understand the problem, but I have a couple of suggestions...

1. Use bigint instead of int (this prevents the 11-15 overflow)
2. Simplify the case statement using some mathematical functions (I can't suggest what since I don't fully understand - but it doesn't 'look' like that would be too difficult)

Ryan Randall

The formula is named "faculty" or "fakulty" something. Don't know the english word for it, but it is spelled with "!".

1! is 1
2! is 2 (1 * 2)
3! is 6 (1 * 2 * 3)
4! is 24 (1 * 2 * 3 * 4)
...
with special case
0! is 1

Antonios formula would look something like

(selections)! / (selection - bets)!

selections is the number of alternatives
bets is the number of alternatives used

It makes sense since you have four places to put your first bet on, and only three remaining to put your second bet on.

There you have all 12 combinations (4! / (4-2)!) of

01. A vs B
02. A vs C
03. A vs D

04. B vs A
05. B vs C
06. B vs D

07. C vs A
08. C vs B
09. C vs D

10. D vs A
11. D vs B
12. D vs C

To remove all "double occurencies" divide by two again

Formula 4! / (4-2)! / 2!

Left is

01. A vs B
02. A vs C
03. A vs D

05. B vs C
06. B vs D

09. C vs D

Still I don't know where 3 occurence come from...

Common formula for placing b bets on a alternatives without "equalities" is

a! / (a-b)! / b!

In the original example 4! / 2! / 2! -> 6 (24 / 2 / 2)

35 alternatives and 7 bets (lotto anyone?) equals 6,724,520 (35! / 28! / 7!), which is a slim chance to win... About 1 in fat chance

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 08:19:41

Well, maybe something based on this. Maybe with some +-1s and some /2s thrown in there somewhere

CREATE function dbo.factorial(@n bigint) returns bigint
as
BEGIN
    declare @i bigint
    if (@n <= 1)
        set @i = 1
    else
        set @i = @n * dbo.factorial(@n - 1)
    return @i
END
go

DECLARE @BET int
DECLARE @SEls int
SELECT @bet = 11
SELECT @sels = 15

select dbo.factorial(@sels-1) / (dbo.factorial(@sels-@bet) * dbo.factorial(@bet-1))

/*results
-------------------- 
1001

--this is the same answer as the 'longhand' version - but I'm not convinced either is right. 
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 09:31:31

Comparison between my formula and Antonio's "long hand" formula.


Bets  Sels  Antonio  Peso  y = Bets/Sels  Peso * y
   2    10        9    45            0.2         9
   3    10       36   120            0.3        36
   4    10       84   210            0.4        84
   5    10      126   252            0.5       126
   6    10      126   210            0.6       126
   7    10       84   120            0.7        84
   8    10       36    45            0.8        36
   9    10        9    10            0.9         9
  10    10        1     1            1.0         1

   2     7        6    21    0.285714286         6
   3     7       15    35    0.428571429        15
   4     7       20    35    0.571428571        20
   5     7       15    21    0.714285714        15
   6     7        6     7    0.857142857         6
   7     7        1     1    1.000000000         1

Conclusion:
I don't understand Antonio's formulas but here it is in one-liner

Sels! / (Sels-Bets)! / Bets! * Bets / Sels

Antonio, can you explain the last part, Bets/Sels?

or simplified as
(Sels - 1)! / (Sels-Bets)! / (Bets - 1)!

Antonio
Posting Yak Master

168 Posts

Posted - 2006-06-02 : 10:05:21

quote:
Originally posted by Peso

Isn't
A versus B
B versus A

2 times only for the occurence of A related any other option?

Can someone please explain for me this occurence?

This would count as one occurence.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

Antonio
Posting Yak Master

168 Posts

Posted - 2006-06-02 : 10:07:17

quote:

Left is

01. A vs B
02. A vs C
03. A vs D

05. B vs C
06. B vs D

09. C vs D

That is right. Each selection only occurs 3 times which is the correct answer!

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 10:07:24

Ryan, I couldn't resist this one

CREATE function dbo.factorial1(@n bigint) returns bigint
as
BEGIN
    declare @i bigint
    if (@n <= 1)
        set @i = 1
    else
        set @i = @n * dbo.factorial1(@n - 1)
    return @i
END

CREATE FUNCTION dbo.PesoFactorial(@n TINYINT) RETURNS BIGINT
AS
BEGIN
    declare @i bigint

    set @i = 1

    while @n > 1
        select  @i = @i * @n,
	        @n = @n - 1

    return @i
END

-- Speed test
declare @c smallint, @s datetime, @dummy bigint

select  @c = 10000,
        @s = getdate()
	
while @c > 1
	select @dummy = dbo.factorial1(20),
               @c = @c - 1

select datediff(ms, @s, getdate()) ms

select  @c = 10000,
        @s = getdate()
	
while @c > 1
	select @dummy = dbo.pesofactorial(20),
               @c = @c - 1

select datediff(ms, @s, getdate()) ms

declare @sels tinyint, @bets tinyint

select @sels = 5, @bets = 2
SELECT dbo.pesofactorial(@sels) / dbo.pesofactorial(@sels - @bets) / dbo.pesofactorial(@bets) * @bets / @sels

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 10:19:07

quote:

01. A vs B
02. A vs C
03. A vs D

05. B vs C
06. B vs D

09. C vs D

That is right. Each selection only occurs 3 times which is the correct answer!

Still don't get it.

A selection is the two bets in one game?
Then A-B and B-A is one selection? A-C and C-A is the second? A-D and D-A is the third?

Since you can't select same bet again (A-A), the occurrence is Sels - 1 ?
Or are we talking about permutation?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 10:36:07

Replace all your formulas with this function:


CREATE FUNCTION dbo.fnFactorial
(
	@n BIGINT
)
RETURNS BIGINT
AS

BEGIN
    DECLARE @i BIGINT

    IF @n >= 0 AND @n <= 20
        SELECT @i = 1

    WHILE @n > 1
        SELECT @i = @i * @n,
               @n = @n - 1

    RETURN @i
END

And calculate with
SELECT dbo.fnFactorial(@Sels) / dbo.fnFactorial(@Sels - @Bets) / dbo.fnFactorial(@Bets) * @Bets / @Sels

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 10:37:53

Peso (Peter

)

Just to be clear...

CREATE function dbo.ryanSomeoneElsesFactorial(@n bigint) returns bigint

I think it originally comes from here...
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5

I must admit, when I posted it, I was thinking it probably would be written better the way you've done it, but was too lazy to worry about it

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 12:59:51

So 2 bets of 4 selections has the occurence of 3?

What is then the occurence with 3 bets of 4 selections? 5?

What is then the occurence with 2 bets of 5 selections? 11?
What is then the occurence with 3 bets of 5 selections? 11?
What is then the occurence with 4 bets of 5 selections? 23?

What is then the occurence with 2 bets of 6 selections? 47?
What is then the occurence with 3 bets of 6 selections? 35?
What is then the occurence with 4 bets of 6 selections? 47?
What is then the occurence with 5 bets of 6 selections? 119?

If that's the case, then "occurence" is

(s! - (s-b)! / b!) / b!

s selections
b bets

SELECT (dbo.fnFactorial(@Sels) - dbo.fnFactorial(@Sels - @Bets) / dbo.fnFactorial(@Bets)) / dbo.fnFactorial(@Bets)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 15:03:36

quote:
Originally posted by Antonio

quote:

Left is

01. A vs B
02. A vs C
03. A vs D

05. B vs C
06. B vs D

09. C vs D

That is right. Each selection only occurs 3 times which is the correct answer!

Each selection means each bet?

A occurs a total of 3 times? So does B, C and D each?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-06 : 07:20:20

quote:
Originally posted by Peso

quote:

01. A vs B
02. A vs C
03. A vs D

05. B vs C
06. B vs D

09. C vs D

That is right. Each selection only occurs 3 times which is the correct answer!

Still don't get it.

A selection is the two bets in one game?
Then A-B and B-A is one selection? A-C and C-A is the second? A-D and D-A is the third?

Since you can't select same bet again (A-A), the occurrence is Sels - 1 ?
Or are we talking about permutation?

Any comment on this, Antonio?

Antonio
Posting Yak Master

168 Posts

Posted - 2006-06-06 : 09:45:23

quote:
Originally posted by Peso

quote:
Originally posted by Peso

quote:

01. A vs B
02. A vs C
03. A vs D

05. B vs C
06. B vs D

09. C vs D

That is right. Each selection only occurs 3 times which is the correct answer!

Still don't get it.

A selection is the two bets in one game?
Then A-B and B-A is one selection? A-C and C-A is the second? A-D and D-A is the third?

Since you can't select same bet again (A-A), the occurrence is Sels - 1 ?
Or are we talking about permutation?

Any comment on this, Antonio?

Hi mate,

Sorry for not getting back you on this earlier. Thanks to you and Ryan. I now have a working solution (with some modifications)

Thank you for all the help. Much appreciated.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources