Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 81 - Create a Game schedule generator in TSQL

This challenge is to generate a game schedule based on the teams given in a table. The key point is each team has to play with another team in each round of the games.

"Share database changes fast
Share SQL code updates, roll back changes you don’t want, inspect line-level differences between object versions, and check a full log of changes, all in SQL Server Management Studio. Start now with a 28-day free trial."

Sample Data

TeamID TeamName LeagueID 
------ -------- --------
1	   Greece   A
2	   Russia   A
3	   France   A
4	   Sweden   A
5	   Italy    A
6	   Portugal A
7	   Poland   A
8	   Croatia  A
9	   England  A
10	   Denmark  A
11	   Spain    A
12	   Germany  A

Expected Results

LeagueID Round Home Team Away Team 
-------- ----- --------- --------- 
A		 1     Greece	 Russia	
A		 1	   France	 Sweden	
A		 1	   Italy	 Portugal  
A		 1	   Poland	 Croatia   
A		 1	   England	 Denmark   
A		 1	   Spain	 Germany   
A		 2	   Denmark	 Spain	    
A		 2	   England	 Croatia   
A		 2	   Greece	 Germany   
A		 2	   Sweden	 Italy	    
A		 2	   Poland	 Portugal  
A		 2	   Russia	 France	
A		 3	   France	 Italy	    
A		 3	   Germany	 Russia	
A		 3	   Croatia	 Denmark   
A		 3	   England	 Poland	
A		 3	   Greece	 Spain	    
A		 3     Portugal  Sweden	
A		 4	   Sweden	 Poland	
A		 4	   Italy	 Greece	
A		 4     Portugal  France	
A		 4     Russia	 Denmark   
A		 4     Germany	 England   
A		 4     Croatia	 Spain	    
A		 5	   Croatia	 Portugal  
A		 5     Denmark	 Germany   
A		 5     England	 Spain	    
A		 5     Greece	 France	
A		 5     Russia	 Sweden	
A		 5     Poland	 Italy	    
A		 6	   Germany	 France	
A		 6     Sweden	 England   
A		 6	   Russia	 Poland	
A		 6     Spain	 Portugal  
A		 6     Croatia	 Italy	    
A		 6     Denmark	 Greece	
A		 7	   Russia	 Croatia   
A		 7     Portugal  Denmark   
A		 7     Sweden	 Greece	
A		 7     Italy	 England   
A		 7     Spain	 France	
A		 7     Germany	 Poland	
A		 8	   England	 Greece	
A		 8     Poland	 Spain	    
A		 8     France	 Denmark   
A		 8     Portugal  Germany   
A		 8     Italy	 Russia	
A		 8     Sweden	 Croatia   
A		 9	   Spain	 Sweden	
A		 9	   Russia	 Portugal  
A		 9	   Germany	 Italy	    
A		 9	   Croatia	 Greece	
A		 9	   Denmark	 Poland	
A		 9     France	 England   
A		 10	   Portugal  England   
A		 10	   Greece	 Poland	
A		 10    Spain	 Russia	
A		 10    Germany	 Sweden	
A		 10    France	 Croatia   
A		 10    Italy	 Denmark   
A		 11    Croatia	 Germany   
A		 11    Spain	 Italy	    
A		 11    Denmark	 Sweden	
A		 11    France	 Poland	
A		 11    Portugal  Greece	
A		 11    England	 Russia	
A		 12    Portugal  Italy	    
A		 12    Denmark	 England   
A		 12    Croatia	 Poland	
A		 12    Russia	 Greece	
A		 12    Germany	 Spain	    
A		 12    Sweden	 France	
A		 13    Germany	 Greece	
A		 13	   Portugal  Poland	
A		 13    Italy	 Sweden	
A		 13    Spain	 Denmark   
A		 13    France	 Russia	
A		 13    Croatia	 England   
A		 14    Denmark	 Croatia   
A		 14    Spain	 Greece	
A		 14    Poland	 England   
A		 14    Italy	 France	
A		 14    Sweden	 Portugal  
A		 14    Russia	 Germany   
A		 15    France	 Portugal  
A		 15    England	 Germany   
A		 15    Denmark	 Russia	
A		 15    Poland	 Sweden	
A		 15    Spain	 Croatia   
A		 15    Greece	 Italy	    
A		 16    Spain	 England   
A		 16    Sweden	 Russia	
A		 16    France	 Greece	
A		 16    Portugal  Croatia   
A		 16    Italy	 Poland	
A		 16    Germany	 Denmark   
A		 17    Poland	 Russia	
A		 17    Italy	 Croatia   
A		 17    Portugal  Spain	    
A		 17    France	 Germany   
A		 17    Greece	 Denmark   
A		 17    England	 Sweden	
A		 18    Greece	 Sweden	
A		 18    France	 Spain	    
A		 18    England	 Italy	    
A		 18    Croatia	 Russia	
A		 18    Poland	 Germany   
A		 18    Denmark	 Portugal  
A		 19    Denmark	 France	
A		 19    Russia	 Italy	    
A		 19    Germany	 Portugal  
A		 19    Greece	 England   
A		 19    Croatia	 Sweden	
A		 19    Spain	 Poland	
A		 20    Italy	 Germany   
A		 20    Poland	 Denmark   
A		 20    Greece	 Croatia   
A		 20    Sweden	 Spain	    
A		 20    England	 France	
A		 20    Portugal  Russia	
A		 21    Russia	 Spain	    
A		 21    Croatia	 France	
A		 21    Sweden	 Germany   
A		 21    England	 Portugal  
A		 21    Denmark	 Italy	    
A		 21    Poland	 Greece	
A		 22    Sweden	 Denmark   
A		 22    Greece	 Portugal  
A		 22    Poland	 France	
A		 22    Germany	 Croatia   
A		 22    Russia	 England   
A		 22    Italy	 Spain

Rules

  1. There should be N/2 matches in each round where N is the number of teams in a league
  2. Every team should play in every round
  3. Each team can play only once per round
  4. Each team should play with every other team twice (once at home and once away)
  5. The input data can have more than one league
  6. The output should be ordered by LeagueID, Round, Home Team
  7. TeamID is unique across all leagues
  8. A team can play in only one league
  9. There can be many correct solutions different from the expected output
  10. There will always be at least 2 teams in a league and the number of teams will always be even

Sample Script

Use the TSQL Script given below to generate the source table and fill them with sample data.

    
IF OBJECT_ID('TC81','U') IS NOT NULL
  DROP TABLE TC81
GO

CREATE TABLE TC81(	
	TeamID INT,
	TeamName VARCHAR(50),
	LeagueID VARCHAR(50)
)
GO
INSERT INTO TC81(TeamID,TeamName,LeagueID)
SELECT 1,'Greece','A' UNION ALL  
SELECT 2,'Russia','A' UNION ALL  
SELECT 3,'France','A' UNION ALL
SELECT 4,'Sweden','A' UNION ALL  
SELECT 5,'Italy','A' UNION ALL
SELECT 6,'Portugal','A' UNION ALL  
SELECT 7,'Poland','A' UNION ALL
SELECT 8,'Croatia','A' UNION ALL  
SELECT 9,'England','A' UNION ALL  
SELECT 10,'Denmark','A' UNION ALL  
SELECT 11,'Spain','A' UNION ALL
SELECT 12,'Germany','A' 

SELECT * FROM TC81
GO

Restrictions

  1. This challenge allows multi-statement solutions. Your solution does not need to be a single-query solution.

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
  3. Your solution can use features from all versions up to and including SQL Server 2012.
"Share database changes fast
Share SQL code updates, roll back changes you don’t want, inspect line-level differences between object versions, and check a full log of changes, all in SQL Server Management Studio. Start now with a 28-day free trial."

puzzles
13 · 13% · 4057
Submit a Solution
Previous Challenge
Next Challenge
4
Liked
11
Comments
28
Solutions



Submit

TSQL Challenge 81 - Create a Game schedule generator in TSQL

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.