SQL Lion

Problem: Mr. Coach has Team A and Team B whom he wishes to play for different countries. Provided the given conditions satisfied:

• Team A and Team B both shouldn’t play in the same country at same time
• Team A and Team B should play at least once for each country.
• Number of matches played will be same as number of locations.

Now Mr. Coach needs little help in scheduling matches for each of his team along with the above constraints. Let’s check out the solution.

Solution: Let’s assume there are 3 countries named ‘India’, ‘Japan’ and ‘China’ where Team A and Team B are going to play. Now I need to arrange both the teams in such a way that for a given day, they should be playing in different countries and each team should get a chance to play in each of the countries.

So, we are provided with only one dataset named “Dim_Location” as below:

Script to get the above result set is:

DECLARE @Dim_Location TABLE (
    Location_Name VARCHAR (30));
 
INSERT  INTO @Dim_Location (Location_Name)
VALUES ('India'),
       ('Japan'),
       ('China'); 
 
SELECT *
FROM   @Dim_Location;

Our output should look as below:

Now let’s figure out some kind of pattern in between this dataset. If I see, both the teams are getting the chance to play in all the countries and none of them are playing in the same country twice or in the same day.

It seems, there are one set of countries i.e. for both the team for each day and if I rearrange the countries as a different way as below, I can have a pattern to schedule the matches.

It will be good if I can implement the above relationship between the countries someway so that I can give some meaning to the result set. Let’s try with assigning some unique numbers to each of the countries.

After assigning the number to each country I got the result set as below:

Query:

DECLARE @Dim_Location TABLE (
    Location_Name VARCHAR (30));
 
INSERT  INTO @Dim_Location (Location_Name)
VALUES  ('India'),
        ('Japan'),
        ('China');
 
;WITH   CTE (RecID, Location_Name)
AS     (SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS RecID,
               Location_Name
        FROM   @Dim_Location)
SELECT *
FROM   CTE;

OUTPUT:

To get more information about CTE, please refer Common Table Expressions (CTE) and for details about ROW_NUMBER(), refer Row_Number.

Now, it’s become easy to frame a query which will give the required result set with all the criteria fulfilled.  The below query will give you the final output.

DECLARE @Dim_Location TABLE (
    Location_Name VARCHAR (30));
 
INSERT  INTO @Dim_Location (Location_Name)
VALUES  	('India'),
		('Japan'),
		('China'); 
;WITH   CTE (RecID, Location_Name)
AS     (SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS RecID,
               Location_Name
        FROM   @Dim_Location)
 
SELECT 'Day ' + CAST (B.RecID AS VARCHAR (10)) AS ScheduledDate,
       A.Location_Name AS TeamA,
       B.Location_Name AS TeamB
FROM   CTE AS A CROSS JOIN CTE AS B
WHERE  A.RecID <> B.RecID
       AND (A.RecID = B.RecID + 1 -- for adjacent relation (1 , 2) and (2 , 3)
            OR (A.RecID = B.RecID - ((SELECT MAX(RecId) FROM   CTE) - 1))); -- for last , first relation (3 , 1)

OUTPUT:

The above query works for any number of locations, consider adding ‘Nepal’, ‘Korea’ and ‘Denmark’, the query will change as below:

DECLARE @Dim_Location TABLE (
    Location_Name VARCHAR (30));
 
INSERT  INTO @Dim_Location (Location_Name)
VALUES   ('India'),
         ('Japan'),
         ('China'),
         ('Nepal'),
         ('Korea'),
         ('Denmark');
 
;WITH   CTE (RecID, Location_Name)
AS     (SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS RecID,
               Location_Name
        FROM   @Dim_Location)
 
SELECT 'Day ' + CAST (B.RecID AS VARCHAR (10)) AS ScheduledDate,
       A.Location_Name AS TeamA,
       B.Location_Name AS TeamB
FROM   CTE AS A CROSS JOIN CTE AS B
WHERE  A.RecID <> B.RecID
       AND (A.RecID = B.RecID + 1 
            OR (A.RecID = B.RecID - ((SELECT MAX(RecId) FROM   CTE) - 1)));

OUTPUT:

Happy T-SQL Programming. Waiting for your valuable feedbacks. ☺