Re: organizing and shuffling records with constraints
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 05/21/04
- Next message: Andrew John: "Re: Question about Grouping in SQL!!"
- Previous message: Tom Moreau: "Re: Index Question"
- In reply to: msnews: "Re: organizing and shuffling records with constraints"
- Next in thread: Joe Celko: "Re: organizing and shuffling records with constraints"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 20 May 2004 20:09:48 -0400
Funny you should mention that. I kept on going:
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 (10)
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
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
create Proc FillBoardRounds
as
set nocount on
declare @Round int
declare @MaxRound int
declare @Iteration int
declare @MaxIterations int
set @MaxRound = (select max (RoundID) from Rounds)
set @MaxIterations = 100
set @Round = 1
set @Iteration = 1
delete BoardRounds
while exists (select * from Matches m
where not exists (select * from BoardRounds br
where br.MatchID = m.MatchID))
and @Iteration <= @MaxIterations
begin
declare @t table (MatchID int primary key, x int)
declare @t2 table (MatchID int primary key, x int)
insert @t2
select
MatchID
, checksum (newid()) as x
from
Matches m
where not exists
(
select
*
from
BoardRounds br
where
br.MatchID = m.MatchID
)
order by
x
insert @t
select
*
from
@t2 t1
where
(select count (*) from Boards) >=
(
select
count (*)
from
@t2 t2
where
t1.x >= t2.x
)
delete @t2
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
set @Iteration = @Iteration + 1
end
go
while exists (select * from Matches m
where not exists (select * from BoardRounds br
where br.MatchID = m.MatchID))
exec FillBoardRounds
go
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
drop proc dbo.FillBoardRounds
-- 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:%23R$53JoPEHA.3012@TK2MSFTNGP09.phx.gbl... I forgot to mention something, it's cool but i am trying to modify the solution so that the number of boards can be Parameterized, in the example we have 8 rounds with 2 boards, if i had 4 boards i should have 4 rounds and the calculation would take in account these params... I will try to update the solution Thanks, /sf "msnews" <stephane.frechette@cactuscommerce.com> wrote in message news:O4aCD4mPEHA.3944@tk2msftngp13.phx.gbl... > 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 > > > > > > > > > > > >
- Next message: Andrew John: "Re: Question about Grouping in SQL!!"
- Previous message: Tom Moreau: "Re: Index Question"
- In reply to: msnews: "Re: organizing and shuffling records with constraints"
- Next in thread: Joe Celko: "Re: organizing and shuffling records with constraints"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
