Dang it! I'm sorry. I lost track of this thread. :blush:

I said I had worked out the math for a Round Robin schedule but not the "Field of Play" rotations like you might need for a bowling league. Then I lost track of this thread.

If you'll forgive me for being a week late, I converted the math that I had worked out to a T-SQL algorithm for solving Round Robin scheduling for virtually any number of teams you may have. It does use the magic of a Tally Table and you can find out how it works to replace certain types of loops at the following URL:

http://www.sqlservercentral.com/articles/T-SQL/62867/

This particular code uses a "zero based" Tally Table. You can easily build such a thing using the following code...

--===== Do this in a nice safe place that everyone has

USE TempDB;

IF OBJECT_ID('TempDB..Tally','U') IS NOT NULL

DROP TABLE Tally;

GO

--===================================================================

-- Create a Tally table from 0 to 11000

--===================================================================

--===== Create and populate the Tally table on the fly.

SELECT TOP 11001

IDENTITY(INT,0,1) AS N

INTO dbo.Tally

FROM Master.sys.ALL_Columns ac1

CROSS JOIN Master.sys.ALL_Columns ac2

;

--===== Add a CLUSTERED Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

;

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

;

GO

Here's the code to create the "Round Robin" schedules using T-SQL. If you really want it to "go horizontal", let me know and I'll write the necessary dynamic SQL to do so.

/**********************************************************************************************************************

Purpose:

Given virtually any number of teams > 0, create a "Round Robin" schedule.

Notes:

1. This code does NOT cycle the last team through "Match" positions (which could be lane pairs for a bowling league).

Separate code would be need for that.

2. This code could be converted to an function or a stored procedure.

Revision History:

Rev 00 - 17 Jan 2012 - Jeff Moden

**********************************************************************************************************************/

--===== Do this in a nice safe place that everyone has

USE TempDB;

--===== Declare and set the number of players present.

-- This could be a parameter in a stored procedure or function.

DECLARE @Teams INT;

SELECT @Teams = 6; --<<< This could be a parameter in a function or a proc

--===== Conditionally drop the working table

IF OBJECT_ID('tempdb..#Team','U') IS NOT NULL

DROP TABLE #Team

;

--===== If the number of players is an odd number, add 1 to the number of players

SELECT @Teams = @Teams + (@Teams % 2)

;

--===== Create the team/reference table table with a preset "Reference" to act as a "ring counter"

SELECT Team = t.N,

Reference = CASE --This is a zero based number

WHEN t.N = @Teams THEN @Teams - 1 --Last position is always last count

WHEN t.N % 2 = 1 THEN t.N/2 --Count odd forwards

WHEN t.N % 2 = 0 THEN (@Teams-t.N)/2+(@Teams/2)-1 --Count all but last even backwards

END,

Match = (t.N - 1) / 2 + 1

INTO #Team

FROM dbo.Tally t

WHERE t.N >= 1 AND t.N <= @Teams

;

--===== Produce the "Round Robin" schedule

WITH

cteBuildRounds AS

( --=== This builds the round information

SELECT [Round] = t.N + 1,

Reference = CASE

WHEN robin.Reference < (@Teams - 1) THEN (robin.Reference + t.N) % (@Teams - 1)

ELSE robin.Reference

END,

Match = robin.Match,

Side = (robin.Team - 1) % 2

FROM #Team robin

CROSS JOIN dbo.Tally t

WHERE t.N BETWEEN 0 AND (@Teams - 2)

) --=== This simply summarizes by match

SELECT br.[Round],

br.Match,

Teams = MAX(CASE WHEN br.Side = 0 THEN CAST(team.Team AS VARCHAR(10)) ELSE '' END) + ' - '

+ MAX(CASE WHEN br.Side = 1 THEN CAST(team.Team AS VARCHAR(10)) ELSE '' END)

FROM cteBuildRounds br

INNER JOIN #Team team

ON team.Reference = br.Reference

GROUP BY br.[Round], br.Match

ORDER BY br.[Round], br.Match

;