SQL Server Round Robin Tournament

Question

I am trying to create a round robin style football tournament where every team plays once (and only once) a week, with the league containing 12 teams.

Using the code below as an example you should be able to see that I have 12 teams. I have used a CTE to Generate the macth fixtures so that every team plays each other once at home and once away (this results in 132 matches).

As there are 12 teams for a total of 132 matches there should be 6 matches every week for 22 weeks.

How would I generate the weeks in which each fixture is taking place?

DECLARE @Temp TABLE(
    TeamName VARCHAR(100),
    MatchWeek INT
)

INSERT INTO @Temp(TeamName) VALUES
('Team1'),
('Team2'),
('Team3'),
('Team4'),
('Team5'),
('Team6'),
('Team7'),
('Team8'),
('Team9'),
('Team10'),
('Team11'),
('Team12');

SELECT t1.Teamname, t2.Teamname
FROM @Temp t1, @Temp t2
WHERE t1.TeamName <> t2.TeamName

Answer 1

This will do it. Demo

As explained in the Wikipedia entry the algorithm was taken from it uses the following positions.

The last team (lexicographic ordering) will be fixed in the green position. All other teams will rotate around the positions 1-11. The fixtures alternate between using the Top and Bottom member of each pair as the Home or Away team to avoid long sequences of these for teams.

In the event that an odd number of teams is presented one team will have no match each week. This is handled below by creating a dummy null row in that case and then using the same algorithm.

WITH Teams
     AS (SELECT TeamName,
                TeamNum = ROW_NUMBER() OVER (ORDER BY TeamName),
                TeamCount = COUNT(*) OVER()
         FROM   @Temp
         /*Purpose of below is to add an extra dummy team if odd number 
           of teams. This null team name will be matched up against competitors 
           having no game that week */
         GROUP  BY TeamName WITH ROLLUP
         HAVING GROUPING(TeamName) = 0
                 OR COUNT(*) %2 = 1),
     Weeks
     AS ( /*We need numbers 1- 11 for a 12 team league etc. 
           Can use the row numbers calculated above for this*/
         SELECT TeamNum AS Week
         FROM   Teams
         WHERE  TeamNum < TeamCount),
     Positioned
     AS (SELECT TeamName,
                TeamNum,
                Week,
                position,
                TeamCount
         FROM   Teams
                CROSS JOIN Weeks
                /*Uses scheduling algorithm from Wikipedia with the last team in fixed position
                  and all other teams rotating around (between positions 1 and 11 in 12 team example)*/
                CROSS APPLY (SELECT CASE
                                      WHEN TeamNum = TeamCount THEN TeamCount
                                      ELSE 1 + ( ( TeamNum + Week - 1 ) % ( TeamCount - 1 ) )
                                    END) CA(position))
SELECT V.*
FROM   Positioned P1
       JOIN Positioned P2
         ON P1.Week = P2.Week
            /*Sum of positions should add up to TeamCount + 1*/
            AND P1.position = 1 + P2.TeamCount - P2.position
            /*Choose Home and Away from alternating Top and Bottom of pair to 
              avoid long runs of either for a team*/
            AND (P2.Week %2 = 0 AND P1.position < P2.position
                 OR P2.Week %2 = 1 AND P1.position > P2.position)
            /*For second half of the season just reversing the "Home" and "Away" teams */
       CROSS APPLY ( VALUES(P1.TeamName, P2.TeamName, P1.Week),
                           (P2.TeamName, P1.TeamName, P1.Week + P1.TeamCount - 1) ) V(HomeTeam, AwayTeam, Week)
/*Exclude any dummy matches if odd number of teams*/
WHERE  V.AwayTeam IS NOT NULL
       AND V.HomeTeam IS NOT NULL
ORDER  BY V.Week 

Alternatively the self join of Positioned can be avoided with some aggregation by swapping out the last part of the above query with this. demo

     Positioned
     AS (SELECT TeamName,
                TeamNum,
                Week,
                position,
                TeamCount,
                /*Sum of opposing positions should add up to TeamCount + 1 so can calculate slot for grouping*/
                Slot = CASE WHEN position <= TeamCount / 2 THEN position ELSE TeamCount + 1 - position END
         FROM   Teams
                CROSS JOIN Weeks
                /*Uses scheduling algorithm from Wikipedia with the last team in fixed position
                  and all other teams rotating around (between positions 1 and 11 in 12 team example)*/
                CROSS APPLY (SELECT CASE
                                      WHEN TeamNum = TeamCount
                                        THEN TeamCount
                                      ELSE 1 + ( ( TeamNum + Week ) % ( TeamCount - 1 ) )
                                    END) CA(position)),
     Matches
     AS (SELECT Week,
                Slot,
                TeamCount,
                TopTeam = MAX(CASE WHEN position = slot THEN TeamName END),
                BottomTeam = MAX(CASE WHEN position <> slot THEN TeamName END)
         FROM   Positioned
         GROUP  BY Week,
                   Slot,
                   TeamCount)
SELECT CA.*
FROM   Matches
       CROSS APPLY (
                   /*Choose Home and Away from alternating Top and Bottom of pair to 
                     avoid long runs of either for a team*/
                   /*First two rows are for alternate weeks in the 1st half of the season */
                   SELECT TopTeam, BottomTeam, Week
                   WHERE  Week %2 = 0
                   UNION ALL
                   SELECT BottomTeam, TopTeam, Week
                   WHERE  Week %2 > 0
                   UNION ALL
                   /*For second half of the season just reversing the "Home" and "Away" teams */
                   SELECT BottomTeam, TopTeam, Week + TeamCount - 1
                   WHERE  Week %2 = 0
                   UNION ALL
                   SELECT TopTeam, BottomTeam, Week + TeamCount - 1
                   WHERE  Week %2 > 0) CA(HomeTeam, AwayTeam, Week)
/*Exclude any dummy matches if odd number of teams*/
WHERE  CA.AwayTeam IS NOT NULL
       AND CA.HomeTeam IS NOT NULL
ORDER  BY CA.Week;