Create a fixture list in SQL Server

I have a table called Teams which currently holds 14 teams (this will be different for different leagues/seasons) and i need to generate a fixture list where each eam will pay eachother home and away. It should work out that each team will play 7 matches per round for 26 rounds making 182 matches.

How do i get them into rounds where a team will only play once in a round and make it all fit nicely into the 26 rounds?
LVL 1
bcoppingAsked:
Who is Participating?
 
BillAn1Commented:
unfortunately this is a difficult problem mathematically, and not so simple to schedule.

below is an algorithm I have used in the past. I cannot lay claim to the underlying mathematics as to how / why it works - I got the logic from a google some time ago. Unfotunately I cannot recall the source to give credit.
Suffice it to say that it does work - it has been used to schedule various leagues with different (odd and even) numbers of teams.

The results are inserted into a table called fixtures which has 3 columns - round int, home_team int, away_team int

The order of the fixtures leaves something to be desired - eg. team 1 plays all their home matches at the start of the season, and all their away matches at the end of the season. You could try just randomising the order of the rounds to see if you get something more random looking. If you need a more sophisticated algorithm, e.g. one that can avoid a team playing more than 2 home games in a row, or where you want to have local derbies scheduled for the end/start of the season etc, I suggest you look at a commercial product that can do this for you. (there are some decent freeware / shareware products available which can do this)


declare @home_team integer
declare @away_team integer

declare @rounds integer
select @rounds = count(*) from teams
if @rounds % 2 = 0 set @rounds = @rounds - 1

declare @matches integer
set @matches = (@rounds + 1)/2
declare @match integer
declare @max_team integer

select @max_team = count(*) from teams

declare @botleft integer
declare @botright integer
declare @topright integer

truncate table fixtures

declare @round integer
set @round = 1
while @round <=  @rounds
begin
      set @match = 1
      while @match <= @matches
      begin
            set @botleft = @rounds/2.0 + 1.25 + @match - @round / 2.0
            set @botright = @rounds/2.0 + @rounds + 2.75 - @match - @round / 2.0
            set @topright = @rounds - @match - @round + 3.0
            if @topRight > @match
            begin
                  set @home_team = @match
                  set @away_team = @topRight
            end
            else
            begin
                  if @botright = @rounds + 1 and @botleft = @match * 2 - 1
                  begin
                        set @home_team = @match
                        set @away_team = @rounds + 1
                  end
                  else
                  begin
                  if @botleft = @botright
                        begin
                              set @home_team = @botleft
                              set @away_team = @rounds + 1
                        end
                        else
                        begin
                              set @home_team = @botleft
                              set @away_team = @botright
                        
                        end
                  
                  end
            end
            if @away_team <= @max_team and @home_team <> @away_team
            insert into fixtures values (@round, @home_team, @away_team)
            set @match = @match + 1
      end
      set @round = @round + 1
end

--- insert home/away swaps
insert into fixtures (round, home_team, away_team)
select round + @rounds, away_team, home_team from fixtures

0
 
Brian CroweDatabase AdministratorCommented:
This sounds like something that should be created in an application and then saved to a table.  SQL is not designed for this kind of processing.

i would start by doing something like.
Round 1: 1vs8, 2vs9, 3vs10, 4vs11, 5vs12, 6vs13, 7vs14
Round 2: 1vs9, 2vs10, 3vs11, 4vs12, 5vs13, 6vs14, 7vs9
Round 3: 1vs10, 2vs11, 3vs12, 4vs13, 5vs14, 6vs9, 7vs10
...
After 13 rounds everyone should have played everyone else once.
Then you can just repeat changing the home/visitor team
Then just jumble up the weeks if you want.
0
 
NievergeltSenior SW DevCommented:
This will return you the requiired pairings:

SELECT t1.city homeTeam, t2.city AS otherTeam
FROM Teams t1 LEFT OUTER JOIN Teams t2 ON t1.teamId <> t2.teamId
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sudheeshthegreatCommented:
declare @teams table
(
t1 int,
t2 int
)

insert into @teams
select t1.name, t2.name from teams t1 inner join teams t2 on t1.name <> t2.name
order by t1.name asc, t2.name desc

declare @fixtures table
(
t1 int,
t2 int
)

declare @TotalRounds int
select @TotalRounds = count(*) - 1 from teams
declare @MatchesPerRound int

declare @round int
set @round = 1
while @round <= @TotalRounds
begin
select @MatchesPerRound = count(*)/2 from teams

declare @match int
set @match = 1

while @match <= @MatchesPerRound
begin
insert into @fixtures
select top 1 t1, t2 from @teams
where t1 not in (select t1 from @fixtures) and t2 not in (select t2 from @fixtures)
and t1 not in (select t2 from @fixtures) and t2 not in (select t1 from @fixtures)
set @match = @match + 1
end

select * from @fixtures

delete @teams
from
@teams t inner join @fixtures f on t.t1 = f.t1 and t.t2 = f.t2

delete from @fixtures
set @round = @round + 1
end
0
 
sudheeshthegreatCommented:
the above code assumes you have a table named 'teams' with a field named 'name'.
0
 
NievergeltSenior SW DevCommented:
My query assumes you have a table Teams with (at least) the fields teamId and city.
0
 
bcoppingAuthor Commented:
Hi sudheeshthegreat

I have tried out your code (was still runnning after 20mins!!) it seemed to keep on going creating empty recordsets. Plus team1 seemed to play all its home matches first (two, possibly three would beacceptible).

BriCrowe this program will be inserted into an ASP.NET application so any VB code that could do this would also be appreciated.
0
 
sudheeshthegreatCommented:
dunno why it took you more than 20 mins. here, i got it executed in 1 second!!
i guess you are better off trying to do this at the code level. with .net, you have many options of creating data structures and getting random team picks.
0
 
bcoppingAuthor Commented:
yep, my mistake. i didn't change all of the names to suit my database!! thanks sudheeshthegreat

this code creates every match combination once. i need it to be home & away. I could just swap team 1 and team 2 around & insert them again. Any ideas how i could get it to merge nicely so that there are not too many home or away matches one after another for each team?
0
 
sudheeshthegreatCommented:
change the
select @TotalRounds = count(*) - 1 from teams
to
select @TotalRounds = (count(*) - 1) * 2 from teams

it was getting fixtures for just 13 rounds (since there are 14 teams). now, it will get fixtures for 26 rounds.

about not having one team to have many simultaneous home or away matches, you can try it 2 ways:
get all this result into a temp table. you have rounds 1 to 26. now, retrieve them in the order 1,14,2,15,3,16,...,13,26.
that should do it.
0
 
bcoppingAuthor Commented:
thanks again, i was just trying something along those lines. However i have come up with a bigger problem. When i tried it with an odd number of teams and it didnt work. One team should sit out each of the rounds but team 1 played in them all.
0
 
bcoppingAuthor Commented:
On closer inspection it is not crating half the matches, if you look at rounds 7 & 8 you will notice that 14 plays 13 then 13 plays 14. Yet teams 1, 2, 3 ... never play an away match.

and when using:
select @TotalRounds = (count(*) - 1) * 2 from teams

there are only 170 mathces not 182, with some rounds not having all 7 matches
0
 
bcoppingAuthor Commented:
Cheers BillAn1, that works a treat.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.