Re: organizing and shuffling records with constraints

From: msnews (stephane.frechette_at_cactuscommerce.com)
Date: 05/20/04 

Date: Thu, 20 May 2004 09:26:02 -0400

Tom this is exactly what i wanted to achiveve
I really appreciate you helping me out with this

Thanks,
/sf

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:uW8sOocPEHA.1276@TK2MSFTNGP11.phx.gbl...
> It took me a while to craft a solution. It does require iteration,
because
> for each round, you have to ensure that no player is double-booked. You
> will note that my population of the Matches table was done at set-level,
> without a loop. A Round is defined as a period where multiple boards are
in
> use. Thus, you don't want the same players playing for a given round.
The
> code is as follows:
>
> create function dbo.GenRows
> (
> @NumRows int
> )
> returns @t table
> (
> RowNum int not null
> primary key
> )
> as
> begin
> -- seed the table
> insert @t values (1)
>
> while @@ROWCOUNT > 0
> insert @t
> select
> t.RowNum + x.MaxRowNum
> from
> @t t
> cross join
> (
> select
> max (RowNum) MaxRowNum
> from
> @t
> ) x
> where
> t.RowNum <= @NumRows - x.MaxRowNum
>
> return
> end
> go
>
> create table Players
> (
> PlayerID int not null
> primary key
> )
>
> insert Players
> select
> RowNum
> from
> dbo.GenRows (6)
> go
>
>
> create table Matches
> (
> MatchID int primary key
> identity
> , Player1ID int not null
> references Players (PlayerID)
> , Player2ID int not null
> references Players (PlayerID)
> , check (Player2ID > Player1ID)
> , unique (Player1ID, Player2ID)
> )
> go
>
> insert Matches (Player1ID, Player2ID)
> select
> a.PlayerID
> , b.PlayerID
> from
> Players a
> join Players b on b.PlayerID > a.PlayerID
> order by
> a.PlayerID
> go
>
> select * from Matches
> go
>
> create table Boards
> (
> BoardID int identity
> primary key
> )
> go
>
> insert Boards default values
> insert Boards default values
> go
>
> create table Rounds
> (
> RoundID int primary key
> )
> go
>
> insert Rounds
> select
> MatchID
> from
> Matches
> where
> MatchID <= ceiling ((select 1. * count (*) from Matches) / (select
count(*)
> from Boards))
> go
>
> create table BoardRounds
> (
> RoundID int not null
> references Rounds
> , BoardID int not null
> references Boards
> , MatchID int not null
> references Matches
> unique
> , primary key (RoundID, BoardID)
> )
> go
>
> declare @t table (MatchID int primary key, x uniqueidentifier)
> declare @Round int
> set @Round = 1
>
> while @Round <= (select max (RoundID) from Rounds)
> begin
>
> insert @t
> select top 2
> MatchID
> , newid() as x
> from
> Matches m
> where not exists
> (
> select
> *
> from
> BoardRounds br
> where
> br.MatchID = m.MatchID
> )
> order by
> x
>
> if not exists
> (
> select
> PlayerID
> , count (*)
> from
> (
> select
> MatchID
> , Player1ID as PlayerID
> from
> Matches
> union all
> select
> MatchID
> , Player2ID as PlayerID
> from
> Matches
> ) as x
> join @t as t on t.MatchID = x.MatchID
> group by
> PlayerID
> having
> count (*) > 1
> )
> begin
> insert BoardRounds
> select
> @Round
> , (select count (*)
> from @t t2
> where t2.MatchID <= t1.MatchID
> )
> , t1.MatchID
> from
> @t t1
>
> set @Round = @Round + 1
> end
>
> delete @t
> end
>
> select
> br.*
> , m.Player1ID
> , m.Player2ID
> from
> BoardRounds br
> join Matches m on m.MatchiD = br.MatchID
> order by
> br.RoundID
> , br.BoardID
> go
>
> drop table BoardRounds, Matches, Players, Boards, Rounds
> drop function dbo.GenRows
>
> --
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
>
> "msnews" <stephane.frechette@cactuscommerce.com> wrote in message
> news:%238kaG8OPEHA.4036@TK2MSFTNGP12.phx.gbl...
> here is what i started from
>
> declare @NumOfParticipant int
> declare @NumberOfTables int
> declare @NumberOfCombinations int
> declare @Counter int
>
> declare @ID int
> declare @ArrayList int
> declare @ArraySum int
>
>
> set @Counter = 1
> set @NumOfParticipant = 6
> set @NumberOfTables = 2
> set @NumberOfCombinations = (@NumOfParticipant * (@NumOfParticipant - 1))/
2
>
> create table #Count
> (
> ID int
> )
>
> create table #Array
> (
> GameID int identity (1, 1),
> P1 int,
> P2 int,
> P1P2 as (P1 + P2),
> IsDone int default(0)
> )
>
> while @Counter < = @NumOfParticipant
> begin
> insert into #Count
> select @Counter
>
> set @Counter = @Counter + 1
> end
>
> declare Array_cursor cursor fast_forward
> for
> select ID
> from #Count
> open Array_cursor
> fetch next from Array_cursor into @ID
> while @@fetch_status = 0
> begin
> set @ArrayList = 1
> while @ArrayList <= @NumOfParticipant
> begin
> select @ArraySum = (@ID + @ArrayList)
>
> if not exists (select P1P2 from #Array where P1P2 = @ArraySum and P2 =
> @ID)
> begin
> insert into #Array (P1, P2)
> values (@ID, @ArrayList)
> end
> set @ArrayList = @ArrayList + 1
> end
> fetch next from Array_cursor into @ID
> end
> close Array_cursor
> deallocate Array_cursor
>
> delete
> from #Array
> where P1 = P2
>
> selec * from #Array
> drop table #Array
>
> --that gives me all the possible matchups (round-robin)
>
> Now without creating addtionnal 'physical tables' for 'boards' and 'games'
> and taking this temp table result
>
>
>
> ID P1 P2
> 2 1 2
> 3 1 3
> 4 1 4
> 5 1 5
> 6 1 6
> 8 2 3
> 9 2 4
> 10 2 5
> 11 2 6
> 13 3 4
> 14 3 5
> 15 3 6
> 17 4 5
> 18 4 6
> 20 5 6
>
>
> I would like to reorder the matchups and make sure that for example P1 = 1
> (PlayerID - > 1) is not assigned (double booked) to play in Game 2 becasue
> he is already booked in Game 1 and
> so on ...
>
> Hope that good enough of an explanation on what i would like to achieve.
> thanks
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:%23XhA9oOPEHA.3096@TK2MSFTNGP09.phx.gbl...
> > How about posting DDL + INSERT's of data + expected results? At the
very
> > least, you should have a table of boards and another that lists which
> games
> > are being played on which board.
> >
> > --
> > Tom
> >
> > ---------------------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "msnews" <stephane.frechette@cactuscommerce.com> wrote in message
> > news:u4cu81NPEHA.3596@tk2msftngp13.phx.gbl...
> > Hi there!
> >
> > Scenario:
> >
> > The table below represents a dart board round-robin tournament matchup
> > where in this case 6 players play one time against each other
> >
> > Column P1 = Participant1
> > Column P2 = Participant2
> >
> > ID P1 P2
> > 1 1 2
> > 2 1 3
> > 3 1 4
> > 4 1 5
> > 5 1 6
> > 6 2 3
> > 7 2 4
> > 8 2 5
> > 9 2 6
> > 10 3 4
> > 11 3 5
> > 12 3 6
> > 13 4 5
> > 14 4 6
> > 15 5 6
> >
> > Lets say that I only have two darts board so only 4 players can play at
a
> > time, i want to able
> > redo (shuffle) a schedule that give me the matchups while not scheduling
> P1
> > or P2 in the current 2 games block selection.
> >
> > Thanks,
> > /sf
> >
> >
>
>

Relevant Pages

  • Re: organizing and shuffling records with constraints
    ... RowNum int not null ... create table Players ... declare @MaxRound int ...
    (microsoft.public.sqlserver.programming)
  • Re: organizing and shuffling records with constraints
    ... for each round, you have to ensure that no player is double-booked. ... you don't want the same players playing for a given round. ... RowNum int not null ... declare @t table ...
    (microsoft.public.sqlserver.programming)
  • Re: organizing and shuffling records with constraints
    ... you don't want the same players playing for a given round. ... >> PlayerID int not null ... >> create table Rounds ... >> create table BoardRounds ...
    (microsoft.public.sqlserver.programming)
  • Re: Tricky problem
    ... problem is to get it rendered as top 8 vs bottom 8, if they are 18 players ... lets say I denormalize things and put a column Rank in #Scores table, ... > Rnd_Nbr INT NOT NULL, ... > This will allow you to track the ranks for each round. ...
    (microsoft.public.sqlserver.programming)
  • Re: help with this game
    ... 'int' object is not callable ... You only want to give players 10 chances to guess the number - but, ... If the player keeps guessing ... > ok now that i know the random function, ...
    (comp.lang.python)