I run a small sports league with 3 divisions. A Ladies Higher, Ladies Lower and Mens division. Depending on the number of teams in a division they may play each other once, or they may play each other twice.
The league runs over 12 weeks (one night a week) (or less weeks if there are less teams entering). There are 24 games each night, 6 time slots on 4 pitches.
As the games are only 20 minutes each team will play 2 games per night, up to a maximum of 3 if there are an odd number of fixtures over the course of the league. Each week can have a mixture of each division playing, its ok for a division to have a week off but it is preferable to have at least 2 of the divisions having matches on any given week.
I have built a table containing all the matches that need playing:
CREATE TABLE [dbo].[TestMatches](
[UniqueMatchId] [int] IDENTITY(1,1) NOT NULL,
[HomeTeam] [varchar](4) NULL,
[AwayTeam] [varchar](4) NULL
)
INSERT INTO dbo.TestMatches
([HomeTeam]
,[AwayTeam])
VALUES
('LH1', 'LH2')
,('LH1', 'LH3')
,('LH1', 'LH4')
,('LH1', 'LH5')
,('LH1', 'LH6')
,('LH1', 'LH7')
,('LH1', 'LH8')
,('LH1', 'LH9')
,('LH1', 'LH10')
,('LH2', 'LH3')
,('LH2', 'LH4')
,('LH2', 'LH5')
,('LH2', 'LH6')
,('LH2', 'LH7')
,('LH2', 'LH8')
,('LH2', 'LH9')
,('LH2', 'LH10')
,('LH3', 'LH4')
,('LH3', 'LH5')
,('LH3', 'LH6')
,('LH3', 'LH7')
,('LH3', 'LH8')
,('LH3', 'LH9')
,('LH3', 'LH10')
,('LH4', 'LH5')
,('LH4', 'LH6')
,('LH4', 'LH7')
,('LH4', 'LH8')
,('LH4', 'LH9')
,('LH4', 'LH10')
,('LH5', 'LH6')
,('LH5', 'LH7')
,('LH5', 'LH8')
,('LH5', 'LH9')
,('LH5', 'LH10')
,('LH6', 'LH7')
,('LH6', 'LH8')
,('LH6', 'LH9')
,('LH6', 'LH10')
,('LH7', 'LH8')
,('LH7', 'LH9')
,('LH7', 'LH10')
,('LH8', 'LH9')
,('LH8', 'LH10')
,('LH9', 'LH10')
,('LH2', 'LH1')
,('LH3', 'LH1')
,('LH4', 'LH1')
,('LH5', 'LH1')
,('LH6', 'LH1')
,('LH7', 'LH1')
,('LH8', 'LH1')
,('LH9', 'LH1')
,('LH10', 'LH1')
,('LH3', 'LH2')
,('LH4', 'LH2')
,('LH5', 'LH2')
,('LH6', 'LH2')
,('LH7', 'LH2')
,('LH8', 'LH2')
,('LH9', 'LH2')
,('LH10', 'LH2')
,('LH4', 'LH3')
,('LH5', 'LH3')
,('LH6', 'LH3')
,('LH7', 'LH3')
,('LH8', 'LH3')
,('LH9', 'LH3')
,('LH10', 'LH3')
,('LH5', 'LH4')
,('LH6', 'LH4')
,('LH7', 'LH4')
,('LH8', 'LH4')
,('LH9', 'LH4')
,('LH10', 'LH4')
,('LH6', 'LH5')
,('LH7', 'LH5')
,('LH8', 'LH5')
,('LH9', 'LH5')
,('LH10', 'LH5')
,('LH7', 'LH6')
,('LH8', 'LH6')
,('LH9', 'LH6')
,('LH10', 'LH6')
,('LH8', 'LH7')
,('LH9', 'LH7')
,('LH10', 'LH7')
,('LH9', 'LH8')
,('LH10', 'LH8')
,('LH10', 'LH9')
,('LL1', 'LL2')
,('LL1', 'LL3')
,('LL1', 'LL4')
,('LL1', 'LL5')
,('LL1', 'LL6')
,('LL1', 'LL7')
,('LL1', 'LL8')
,('LL1', 'LL9')
,('LL1', 'LL10')
,('LL1', 'LL11')
,('LL1', 'LL12')
,('LL1', 'LL13')
,('LL1', 'LL14')
,('LL2', 'LL3')
,('LL2', 'LL4')
,('LL2', 'LL5')
,('LL2', 'LL6')
,('LL2', 'LL7')
,('LL2', 'LL8')
,('LL2', 'LL9')
,('LL2', 'LL10')
,('LL2', 'LL11')
,('LL2', 'LL12')
,('LL2', 'LL13')
,('LL2', 'LL14')
,('LL3', 'LL4')
,('LL3', 'LL5')
,('LL3', 'LL6')
,('LL3', 'LL7')
,('LL3', 'LL8')
,('LL3', 'LL9')
,('LL3', 'LL10')
,('LL3', 'LL11')
,('LL3', 'LL12')
,('LL3', 'LL13')
,('LL3', 'LL14')
,('LL4', 'LL5')
,('LL4', 'LL6')
,('LL4', 'LL7')
,('LL4', 'LL8')
,('LL4', 'LL9')
,('LL4', 'LL10')
,('LL4', 'LL11')
,('LL4', 'LL12')
,('LL4', 'LL13')
,('LL4', 'LL14')
,('LL5', 'LL6')
,('LL5', 'LL7')
,('LL5', 'LL8')
,('LL5', 'LL9')
,('LL5', 'LL10')
,('LL5', 'LL11')
,('LL5', 'LL12')
,('LL5', 'LL13')
,('LL5', 'LL14')
,('LL6', 'LL7')
,('LL6', 'LL8')
,('LL6', 'LL9')
,('LL6', 'LL10')
,('LL6', 'LL11')
,('LL6', 'LL12')
,('LL6', 'LL13')
,('LL6', 'LL14')
,('LL7', 'LL8')
,('LL7', 'LL9')
,('LL7', 'LL10')
,('LL7', 'LL11')
,('LL7', 'LL12')
,('LL7', 'LL13')
,('LL7', 'LL14')
,('LL8', 'LL9')
,('LL8', 'LL10')
,('LL8', 'LL11')
,('LL8', 'LL12')
,('LL8', 'LL13')
,('LL8', 'LL14')
,('LL9', 'LL10')
,('LL9', 'LL11')
,('LL9', 'LL12')
,('LL9', 'LL13')
,('LL9', 'LL14')
,('LL10', 'LL11')
,('LL10', 'LL12')
,('LL10', 'LL13')
,('LL10', 'LL14')
,('LL11', 'LL12')
,('LL11', 'LL13')
,('LL11', 'LL14')
,('LL12', 'LL13')
,('LL12', 'LL14')
,('LL13', 'LL14')
,('M1', 'M2')
,('M1', 'M3')
,('M1', 'M4')
,('M1', 'M5')
,('M1', 'M6')
,('M1', 'M7')
,('M1', 'M8')
,('M1', 'M9')
,('M2', 'M3')
,('M2', 'M4')
,('M2', 'M5')
,('M2', 'M6')
,('M2', 'M7')
,('M2', 'M8')
,('M2', 'M9')
,('M3', 'M4')
,('M3', 'M5')
,('M3', 'M6')
,('M3', 'M7')
,('M3', 'M8')
,('M3', 'M9')
,('M4', 'M5')
,('M4', 'M6')
,('M4', 'M7')
,('M4', 'M8')
,('M4', 'M9')
,('M5', 'M6')
,('M5', 'M7')
,('M5', 'M8')
,('M5', 'M9')
,('M6', 'M7')
,('M6', 'M8')
,('M6', 'M9')
,('M7', 'M8')
,('M7', 'M9')
,('M8', 'M9')
,('M2', 'M1')
,('M3', 'M1')
,('M4', 'M1')
,('M5', 'M1')
,('M6', 'M1')
,('M7', 'M1')
,('M8', 'M1')
,('M9', 'M1')
,('M3', 'M2')
,('M4', 'M2')
,('M5', 'M2')
,('M6', 'M2')
,('M7', 'M2')
,('M8', 'M2')
,('M9', 'M2')
,('M4', 'M3')
,('M5', 'M3')
,('M6', 'M3')
,('M7', 'M3')
,('M8', 'M3')
,('M9', 'M3')
,('M5', 'M4')
,('M6', 'M4')
,('M7', 'M4')
,('M8', 'M4')
,('M9', 'M4')
,('M6', 'M5')
,('M7', 'M5')
,('M8', 'M5')
,('M9', 'M5')
,('M7', 'M6')
,('M8', 'M6')
,('M9', 'M6')
,('M8', 'M7')
,('M9', 'M7')
,('M9', 'M8')
I have also built a table with all the available time slots:
CREATE TABLE dbo.TestPitchTimes(
[UniqueSlotId] [int] IDENTITY(1,1) NOT NULL,
WeekNo varchar(6) NULL,
PitchNo varchar(2) NULL,
TimeSlot char(4) NULL
)
INSERT INTO dbo.TestPitchTimes
(WeekNo,
PitchNo,
TimeSlot
)
VALUES
('Week1', 'P1', '1900')
,('Week1', 'P1', '1925')
,('Week1', 'P1', '1950')
,('Week1', 'P1', '2015')
,('Week1', 'P1', '2040')
,('Week1', 'P1', '2105')
,('Week1', 'P2', '1900')
,('Week1', 'P2', '1925')
,('Week1', 'P2', '1950')
,('Week1', 'P2', '2015')
,('Week1', 'P2', '2040')
,('Week1', 'P2', '2105')
,('Week1', 'P3', '1900')
,('Week1', 'P3', '1925')
,('Week1', 'P3', '1950')
,('Week1', 'P3', '2015')
,('Week1', 'P3', '2040')
,('Week1', 'P3', '2105')
,('Week1', 'P4', '1900')
,('Week1', 'P4', '1925')
,('Week1', 'P4', '1950')
,('Week1', 'P4', '2015')
,('Week1', 'P4', '2040')
,('Week1', 'P4', '2105')
,('Week2', 'P1', '1900')
,('Week2', 'P1', '1925')
,('Week2', 'P1', '1950')
,('Week2', 'P1', '2015')
,('Week2', 'P1', '2040')
,('Week2', 'P1', '2105')
,('Week2', 'P2', '1900')
,('Week2', 'P2', '1925')
,('Week2', 'P2', '1950')
,('Week2', 'P2', '2015')
,('Week2', 'P2', '2040')
,('Week2', 'P2', '2105')
,('Week2', 'P3', '1900')
,('Week2', 'P3', '1925')
,('Week2', 'P3', '1950')
,('Week2', 'P3', '2015')
,('Week2', 'P3', '2040')
,('Week2', 'P3', '2105')
,('Week2', 'P4', '1900')
,('Week2', 'P4', '1925')
,('Week2', 'P4', '1950')
,('Week2', 'P4', '2015')
,('Week2', 'P4', '2040')
,('Week2', 'P4', '2105')
,('Week3', 'P1', '1900')
,('Week3', 'P1', '1925')
,('Week3', 'P1', '1950')
,('Week3', 'P1', '2015')
,('Week3', 'P1', '2040')
,('Week3', 'P1', '2105')
,('Week3', 'P2', '1900')
,('Week3', 'P2', '1925')
,('Week3', 'P2', '1950')
,('Week3', 'P2', '2015')
,('Week3', 'P2', '2040')
,('Week3', 'P2', '2105')
,('Week3', 'P3', '1900')
,('Week3', 'P3', '1925')
,('Week3', 'P3', '1950')
,('Week3', 'P3', '2015')
,('Week3', 'P3', '2040')
,('Week3', 'P3', '2105')
,('Week3', 'P4', '1900')
,('Week3', 'P4', '1925')
,('Week3', 'P4', '1950')
,('Week3', 'P4', '2015')
,('Week3', 'P4', '2040')
,('Week3', 'P4', '2105')
,('Week4', 'P1', '1900')
,('Week4', 'P1', '1925')
,('Week4', 'P1', '1950')
,('Week4', 'P1', '2015')
,('Week4', 'P1', '2040')
,('Week4', 'P1', '2105')
,('Week4', 'P2', '1900')
,('Week4', 'P2', '1925')
,('Week4', 'P2', '1950')
,('Week4', 'P2', '2015')
,('Week4', 'P2', '2040')
,('Week4', 'P2', '2105')
,('Week4', 'P3', '1900')
,('Week4', 'P3', '1925')
,('Week4', 'P3', '1950')
,('Week4', 'P3', '2015')
,('Week4', 'P3', '2040')
,('Week4', 'P3', '2105')
,('Week4', 'P4', '1900')
,('Week4', 'P4', '1925')
,('Week4', 'P4', '1950')
,('Week4', 'P4', '2015')
,('Week4', 'P4', '2040')
,('Week4', 'P4', '2105')
,('Week5', 'P1', '1900')
,('Week5', 'P1', '1925')
,('Week5', 'P1', '1950')
,('Week5', 'P1', '2015')
,('Week5', 'P1', '2040')
,('Week5', 'P1', '2105')
,('Week5', 'P2', '1900')
,('Week5', 'P2', '1925')
,('Week5', 'P2', '1950')
,('Week5', 'P2', '2015')
,('Week5', 'P2', '2040')
,('Week5', 'P2', '2105')
,('Week5', 'P3', '1900')
,('Week5', 'P3', '1925')
,('Week5', 'P3', '1950')
,('Week5', 'P3', '2015')
,('Week5', 'P3', '2040')
,('Week5', 'P3', '2105')
,('Week5', 'P4', '1900')
,('Week5', 'P4', '1925')
,('Week5', 'P4', '1950')
,('Week5', 'P4', '2015')
,('Week5', 'P4', '2040')
,('Week5', 'P4', '2105')
,('Week6', 'P1', '1900')
,('Week6', 'P1', '1925')
,('Week6', 'P1', '1950')
,('Week6', 'P1', '2015')
,('Week6', 'P1', '2040')
,('Week6', 'P1', '2105')
,('Week6', 'P2', '1900')
,('Week6', 'P2', '1925')
,('Week6', 'P2', '1950')
,('Week6', 'P2', '2015')
,('Week6', 'P2', '2040')
,('Week6', 'P2', '2105')
,('Week6', 'P3', '1900')
,('Week6', 'P3', '1925')
,('Week6', 'P3', '1950')
,('Week6', 'P3', '2015')
,('Week6', 'P3', '2040')
,('Week6', 'P3', '2105')
,('Week6', 'P4', '1900')
,('Week6', 'P4', '1925')
,('Week6', 'P4', '1950')
,('Week6', 'P4', '2015')
,('Week6', 'P4', '2040')
,('Week6', 'P4', '2105')
,('Week7', 'P1', '1900')
,('Week7', 'P1', '1925')
,('Week7', 'P1', '1950')
,('Week7', 'P1', '2015')
,('Week7', 'P1', '2040')
,('Week7', 'P1', '2105')
,('Week7', 'P2', '1900')
,('Week7', 'P2', '1925')
,('Week7', 'P2', '1950')
,('Week7', 'P2', '2015')
,('Week7', 'P2', '2040')
,('Week7', 'P2', '2105')
,('Week7', 'P3', '1900')
,('Week7', 'P3', '1925')
,('Week7', 'P3', '1950')
,('Week7', 'P3', '2015')
,('Week7', 'P3', '2040')
,('Week7', 'P3', '2105')
,('Week7', 'P4', '1900')
,('Week7', 'P4', '1925')
,('Week7', 'P4', '1950')
,('Week7', 'P4', '2015')
,('Week7', 'P4', '2040')
,('Week7', 'P4', '2105')
,('Week8', 'P1', '1900')
,('Week8', 'P1', '1925')
,('Week8', 'P1', '1950')
,('Week8', 'P1', '2015')
,('Week8', 'P1', '2040')
,('Week8', 'P1', '2105')
,('Week8', 'P2', '1900')
,('Week8', 'P2', '1925')
,('Week8', 'P2', '1950')
,('Week8', 'P2', '2015')
,('Week8', 'P2', '2040')
,('Week8', 'P2', '2105')
,('Week8', 'P3', '1900')
,('Week8', 'P3', '1925')
,('Week8', 'P3', '1950')
,('Week8', 'P3', '2015')
,('Week8', 'P3', '2040')
,('Week8', 'P3', '2105')
,('Week8', 'P4', '1900')
,('Week8', 'P4', '1925')
,('Week8', 'P4', '1950')
,('Week8', 'P4', '2015')
,('Week8', 'P4', '2040')
,('Week8', 'P4', '2105')
,('Week9', 'P1', '1900')
,('Week9', 'P1', '1925')
,('Week9', 'P1', '1950')
,('Week9', 'P1', '2015')
,('Week9', 'P1', '2040')
,('Week9', 'P1', '2105')
,('Week9', 'P2', '1900')
,('Week9', 'P2', '1925')
,('Week9', 'P2', '1950')
,('Week9', 'P2', '2015')
,('Week9', 'P2', '2040')
,('Week9', 'P2', '2105')
,('Week9', 'P3', '1900')
,('Week9', 'P3', '1925')
,('Week9', 'P3', '1950')
,('Week9', 'P3', '2015')
,('Week9', 'P3', '2040')
,('Week9', 'P3', '2105')
,('Week9', 'P4', '1900')
,('Week9', 'P4', '1925')
,('Week9', 'P4', '1950')
,('Week9', 'P4', '2015')
,('Week9', 'P4', '2040')
,('Week9', 'P4', '2105')
,('Week10', 'P1', '1900')
,('Week10', 'P1', '1925')
,('Week10', 'P1', '1950')
,('Week10', 'P1', '2015')
,('Week10', 'P1', '2040')
,('Week10', 'P1', '2105')
,('Week10', 'P2', '1900')
,('Week10', 'P2', '1925')
,('Week10', 'P2', '1950')
,('Week10', 'P2', '2015')
,('Week10', 'P2', '2040')
,('Week10', 'P2', '2105')
,('Week10', 'P3', '1900')
,('Week10', 'P3', '1925')
,('Week10', 'P3', '1950')
,('Week10', 'P3', '2015')
,('Week10', 'P3', '2040')
,('Week10', 'P3', '2105')
,('Week10', 'P4', '1900')
,('Week10', 'P4', '1925')
,('Week10', 'P4', '1950')
,('Week10', 'P4', '2015')
,('Week10', 'P4', '2040')
,('Week10', 'P4', '2105')
,('Week11', 'P1', '1900')
,('Week11', 'P1', '1925')
,('Week11', 'P1', '1950')
,('Week11', 'P1', '2015')
,('Week11', 'P1', '2040')
,('Week11', 'P1', '2105')
,('Week11', 'P2', '1900')
,('Week11', 'P2', '1925')
,('Week11', 'P2', '1950')
,('Week11', 'P2', '2015')
,('Week11', 'P2', '2040')
,('Week11', 'P2', '2105')
,('Week11', 'P3', '1900')
,('Week11', 'P3', '1925')
,('Week11', 'P3', '1950')
,('Week11', 'P3', '2015')
,('Week11', 'P3', '2040')
,('Week11', 'P3', '2105')
,('Week11', 'P4', '1900')
,('Week11', 'P4', '1925')
,('Week11', 'P4', '1950')
,('Week11', 'P4', '2015')
,('Week11', 'P4', '2040')
,('Week11', 'P4', '2105')
,('Week12', 'P1', '1900')
,('Week12', 'P1', '1925')
,('Week12', 'P1', '1950')
,('Week12', 'P1', '2015')
,('Week12', 'P1', '2040')
,('Week12', 'P1', '2105')
,('Week12', 'P2', '1900')
,('Week12', 'P2', '1925')
,('Week12', 'P2', '1950')
,('Week12', 'P2', '2015')
,('Week12', 'P2', '2040')
,('Week12', 'P2', '2105')
,('Week12', 'P3', '1900')
,('Week12', 'P3', '1925')
,('Week12', 'P3', '1950')
,('Week12', 'P3', '2015')
,('Week12', 'P3', '2040')
,('Week12', 'P3', '2105')
,('Week12', 'P4', '1900')
,('Week12', 'P4', '1925')
,('Week12', 'P4', '1950')
,('Week12', 'P4', '2015')
,('Week12', 'P4', '2040')
,('Week12', 'P4', '2105')
What I need to be able to do is run a loop (maybe?) that assigns the matches to each week so that teams play >= 2 or <= 3 matches per week (maximising the number of weeks playing 2 games), and if playing each other twice during the league do not play both the home and away match on the same week.
I am quite new to SQL, I feel like it is possible but I don't know the code to do it and I haven't been able to find anything that matches the complexity of my league! Could be a similar problem to school timetables, 4 classrooms, 6 lesson times, and pairs of students needed in each available slot?
I'm currently working out the schedule manually which takes a long time every year as the number of teams is always changing so I cant reuse previous schedules. I'm hoping this can speed up the process. Thank you very much in advance for any help you can give.
EDIT: The results I would hope to see is:
WeekNo, UniqueMatchId
Week1, 1
Week1, 2
Week1, 10 ...etc
I first want to establish which games to play each week, then to work out what time each game should be played (so teams aren't playing 2 games at once) but that's another question!
