carl Brooks carl Brooks - 5 months ago 12
SQL Question

Converting Oracle code to SQL Server for a fixture list

I have been kindly given an oracle solution to a particular problem but been struggling to convert it to SQL Server code. I get no errors but get no results. Below is my version using SQL Server and I have provided a link to the question, so you can see the answer I am referring to (the answer is the one marked as best answer).

Here is the link: How to code a certain maths algorithm

Attempted SQL code:

WITH League_Teams (id, leagueid, idx, is_fake, num_teams, num_fake) AS (
-- Generate a unique-per-league index for each team that is between 0
-- and the (number of teams - 1) and calculate the number of teams
-- and if this is an odd number then generate a fake team as well.
SELECT TeamID,
LeagueID,
ROW_NUMBER() OVER ( PARTITION BY LeagueID ORDER BY TeamID ) - 1,
0,
COUNT(1) OVER ( PARTITION BY LeagueID ),
(COUNT(1) OVER ( PARTITION BY LeagueID ) % 2)
FROM Team
UNION ALL
SELECT NULL,
LeagueID,
COUNT(1),
1,
COUNT(1),
1
FROM Team
GROUP BY LeagueID
HAVING COUNT(1) % 2 > 0
),
cte ( home_idx, away_idx, week_number, leagueID, num_teams, num_fake ) AS (
-- Calculate round 1 games
SELECT idx,
num_teams + num_fake - 1 - idx,
1,
LeagueID,
num_teams,
num_fake
FROM league_teams
WHERE 2 * idx < num_teams
UNION ALL
-- Generate successive rounds with the two cases when the away team has the maximum index or otherwise.
SELECT CASE away_idx
WHEN num_teams + num_fake - 1
THEN home_idx + 1
ELSE home_idx + 1 % num_teams + num_fake -1
END,
CASE away_idx
WHEN num_teams + num_fake - 1
THEN away_idx
ELSE away_idx + 1 % num_teams + num_fake - 1
END,
week_number + 1,
LeagueID,
num_teams,
num_fake
FROM cte
WHERE week_number < num_teams + num_fake - 1
)
INSERT INTO dbo.Fixture
-- Join the cte results back to the League_Teams table to convert
-- Indexes used in calculation back to the actual team ids.
SELECT rn,
week_number,
NULL,
h.id,
a.id,
c.leagueid
FROM (
--Keeps the results in a nice order.
SELECT ROW_NUMBER() OVER (ORDER BY LeagueID) AS rn,
t.*
FROM (
-- Duplicate the results swapping home and away.
SELECT week_number,
home_idx,
away_idx,
LeagueId
FROM cte
UNION ALL
SELECT week_number + num_teams + num_fake - 1,
away_idx,
home_idx,
LeagueId
FROM cte
) t
) c
INNER JOIN League_Teams h
ON ( c.home_idx = h.idx AND c.leagueId = h.leagueID )
INNER JOIN League_Teams a
ON ( c.away_idx = a.idx AND c.leagueId = a.leagueID )
ORDER BY rn;

Answer

Below code might help.

Change made:

Removed column list from CTE declaration and used column aliases inside CTE.

WITH League_Teams AS (
  -- Generate a unique-per-league index for each team that is between 0
  -- and the (number of teams - 1) and calculate the number of teams
  -- and if this is an odd number then generate a fake team as well.
  SELECT TeamID AS id,
         LeagueID AS leagueid,
         ROW_NUMBER() OVER ( PARTITION BY LeagueID ORDER BY TeamID ) - 1 AS idx,
         0 AS is_fake,
         COUNT(1) OVER ( PARTITION BY LeagueID ) AS num_teams,
         (COUNT(1) OVER ( PARTITION BY LeagueID ) % 2) AS num_fake
  FROM Team
  UNION ALL
  SELECT NULL,
         LeagueID,
         COUNT(1),
         1,
         COUNT(1),
         1
  FROM   Team
  GROUP BY LeagueID
  HAVING COUNT(1) % 2  > 0
),
cte AS (
  -- Calculate round 1 games
  SELECT idx AS home_idx,
         num_teams + num_fake - 1 - idx AS away_idx,
         1 AS week_number,
         LeagueID AS leagueID,
         num_teams,
         num_fake
  FROM   league_teams
  WHERE  2 * idx < num_teams
UNION ALL
  --  Generate successive rounds with the two cases when the away team has the maximum index or otherwise.
  SELECT CASE away_idx
           WHEN num_teams + num_fake - 1
           THEN home_idx + 1
           ELSE home_idx + 1 % num_teams + num_fake -1
           END,
         CASE away_idx
           WHEN num_teams + num_fake - 1
           THEN away_idx
           ELSE away_idx + 1 % num_teams + num_fake - 1
           END,
        week_number + 1,
        LeagueID,
        num_teams,
        num_fake
  FROM  cte
  WHERE week_number < num_teams + num_fake - 1
)
INSERT INTO dbo.Fixture
-- Join the cte results back to the League_Teams table to convert
-- Indexes used in calculation back to the actual team ids.
SELECT rn,
       week_number,
       NULL,
       h.id,
       a.id,
       c.leagueid
FROM   (
          --Keeps the results in a nice order.
        SELECT ROW_NUMBER() OVER (ORDER BY LeagueID)  AS rn,
                t.*
         FROM   (
           -- Duplicate the results swapping home and away.
           SELECT week_number,
                  home_idx,
                  away_idx,
                  LeagueId
           FROM   cte
           UNION ALL
           SELECT week_number + num_teams + num_fake - 1,
                  away_idx,
                  home_idx,
                  LeagueId
           FROM   cte
         ) t
       ) c
       INNER JOIN League_Teams h
       ON ( c.home_idx = h.idx AND c.leagueId = h.leagueID )
       INNER JOIN League_Teams a
       ON ( c.away_idx = a.idx AND c.leagueId = a.leagueID )
ORDER BY rn;