Breaking News: Grepper is joining You.com. Read the official announcement!

SQL Server Round Robin Tournament

e-mallz contact answered on August 17, 2022 Popularity 2/10 Helpfulness 1/10

answer SQL Server Round Robin Tournament

SQL Server Round Robin Tournament

Comment

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

Popularity 2/10 Helpfulness 1/10 Language sql

Source: sqlerrors.com

Tags: round-robin sql sql-server tournament

Link to this answer
Share Copy Link

Contributed on Aug 17 2022

e-mallz contact

0 Answers Avg Quality 2/10

SQL Server Round Robin Tournament

Contents

More Related Answers

SQL Server Round Robin Tournament

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.