League Fixture Generation code

[G12Consult]

I have the following code which generates fixtures, however I would like to modify the code so I can use the following variables:

- Start Date / End Date of Season. So generate fixtures for each saturday or sunday between those two dates
- Only schedule matches for specific times
- Assign how many times each team plays one another.
- Random venue selection.

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE spCreateFixtures

AS
TRUNCATE TABLE FIXTURES
DECLARE @MATCH_NUMBER INT
SET @MATCH_NUMBER = 1
BEGIN

WITH Teams



	
	/* GET A LIST OF TEAMS FROM THE TEAM TABLE & PROVIDE COUNT OF TEAMS*/
     AS (SELECT TEAM_NAME,
                TEAM_NUM = ROW_NUMBER() OVER (ORDER BY TEAM_NAME),
                TEAM_COUNT = COUNT(*) OVER()
         FROM   TEAM_INFO

	/*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 TEAM_NAME WITH ROLLUP
         HAVING GROUPING(TEAM_NAME) = 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 TEAM_NUM AS Week
         FROM   Teams
         WHERE  TEAM_NUM < TEAM_COUNT),
     Positioned
     AS (SELECT TEAM_NAME,
                TEAM_NUM,
                Week,
                position,
                TEAM_COUNT
         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 TEAM_NUM = TEAM_COUNT THEN TEAM_COUNT
                                      ELSE 1 + ( ( TEAM_NUM + Week - 1 ) % ( TEAM_COUNT - 1 ) )
                                    END) CA(position))



INSERT INTO FIXTURES (WEEK_NUMBER
						, HOME_TEAM
						, AWAY_TEAM
						, MATCH_NUMBER
						)

SELECT V.Week, V.HomeTeam, V.AwayTeam, @MATCH_NUMBER + 1
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.TEAM_COUNT - 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.TEAM_NAME, P2.TEAM_NAME, P1.Week),
                           (P2.TEAM_NAME, P1.TEAM_NAME, P1.Week + P1.TEAM_COUNT - 1) ) V(HomeTeam, AwayTeam, Week)

/*Exclude any BYE matches if odd number of teams*/
--WHERE  V.AwayTeam IS NOT NULL
--       AND V.HomeTeam IS NOT NULL

ORDER  BY V.Week 

UPDATE FIXTURES
SET HOME_TEAM = 'Bye' 
WHERE HOME_TEAM IS NULL

UPDATE FIXTURES
SET AWAY_TEAM = 'Bye' 
WHERE AWAY_TEAM IS NULL

END
GO]