How to organize lines in a union

Question

Having a slight problem with my results, I am trying to create a list of fixtures for each team, playing at home and away. Ive almost got it working, but the problem I have is showing in the following results:

I have two teams that play with each other in the first week of the week and away, and the same two teams play with each other at home and away for a week.

What should this be displayed when I insert the data and read that it is week 1, team 4 at home plays team command 18, which is the way, and then back fastening is played back in week 4.

In other words, lines 2 and 3 are incorrect and only lines 1 and 4 should be displayed. What do I need to change in the code below to make this work?

INSERT INTO dbo.Fixture (WeekNumber, HomeTeamID, AwayTeamID, FixtureDate, LeagueID)
SELECT
    ROW_NUMBER() OVER (PARTITION BY h.teamID ORDER BY h.TeamID, a.TeamID, h.LeagueID) AS WeekNumber,
    h.TeamID,
    a.TeamID,
    DATEADD(day,(ROW_NUMBER() OVER (ORDER BY h.LeagueID)-1)*7,@StartFixtureWeek) AS FixtureWeek, 
    h.LeagueID
FROM dbo.Team h
CROSS JOIN dbo.Team a
WHERE h.TeamID <> a.TeamID
AND h.LeagueID = a.LeagueID
UNION
SELECT
    ROW_NUMBER() OVER (PARTITION BY a.teamID ORDER BY h.TeamID, a.TeamID, h.LeagueID) AS WeekNumber,
    h.TeamID,
    a.TeamID,
    DATEADD(day,(ROW_NUMBER() OVER (ORDER BY a.LeagueID)-1)*7,@StartFixtureWeek) AS FixtureWeek, 
    h.LeagueID
FROM dbo.Team h
CROSS JOIN dbo.Team a
WHERE h.TeamID <> a.TeamID
AND h.LeagueID = a.LeagueID


select * from dbo.Fixture
WHERE (HomeTeamID = 4 AND AwayTeamID = 18) OR (HomeTeamID = 18 AND AwayTeamID = 4)

UPDATE:

:

WeekNumber  HomeTeamID   AwayTeamID  FixtureWeek  LeagueID
1           1             4          10-06-2016         1
2           1             3          17-06-2016         1
3           1             2          24-06-2016         1
4           4             1          30-06-2016         1
5           3             1          06-07-2016         1
6           2             1          13-07-2016         1
1           2             3          10-06-2016         1
2           2             4          17-06-2016         1
3           3             4          24-06-2016         1
4           3             2          30-06-2016         1
5           4             2          06-07-2016         1
6           4             3          13-07-2016         1
1           5             8          10-06-2016         2
2           5             7          17-06-2016         2
3           5             6          24-06-2016         2
4           8             5          30-06-2016         2
5           7             5          06-07-2016         2
6           6             5          13-07-2016         2
1           6             7          10-06-2016         2
2           6             8          17-06-2016         2
3           7             8          24-06-2016         2
4           7             6          30-06-2016         2
5           8             6          06-07-2016         2
6           8             7          13-07-2016         2

, (LeagueID 1 LeagueID 2)

1 4 (TeamID) - 1, 2, 3, 4 - , .

2 4 (TeamID) - 5, 6, 7, 8 - , .

7 (, , ).

, , , , , .. , , , .

Answer 1

CROSS JOIN h.TeamID <> a.TeamID , .. (1,2) (2,1). , , , WHERE h.TeamID > a.TeamID h.TeamID < a.TeamID.

, LeagueID. - , .

, 6 .

, CROSS JOIN, <> UNION ROW_NUMBER.

: UNION, . UNION ALL.

2: , CROSS JOIN INNER JOIN . . , CROSS JOIN , - .

DECLARE @T TABLE (TeamID int, LeagueID int);

INSERT INTO @T (TeamID, LeagueID) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 2),
(6, 2),
(7, 2),
(8, 2);

Query

SELECT
    h.TeamID AS HomeTeamID
    ,a.TeamID AS AwayTeamID
    ,h.LeagueID
    ,ROW_NUMBER() OVER 
        (PARTITION BY h.LeagueID ORDER BY h.TeamID, a.TeamID) AS WeekNumber
    ,1 AS SortOrder
FROM
    @T AS h
    CROSS JOIN @T AS a
WHERE
    h.LeagueID = a.LeagueID
    AND h.TeamID < a.TeamID

UNION ALL

SELECT
    h.TeamID AS HomeTeamID
    ,a.TeamID AS AwayTeamID
    ,h.LeagueID
    ,ROW_NUMBER() OVER 
        (PARTITION BY h.LeagueID ORDER BY a.TeamID DESC, h.TeamID DESC) AS WeekNumber
    ,2 AS SortOrder
FROM
    @T AS h
    CROSS JOIN @T AS a
WHERE
    h.LeagueID = a.LeagueID
    AND h.TeamID > a.TeamID

ORDER BY
    LeagueID
    ,SortOrder
    ,WeekNumber
;

+------------+------------+----------+------------+-----------+
| HomeTeamID | AwayTeamID | LeagueID | WeekNumber | SortOrder |
+------------+------------+----------+------------+-----------+
|          1 |          2 |        1 |          1 |         1 |
|          1 |          3 |        1 |          2 |         1 |
|          1 |          4 |        1 |          3 |         1 |
|          2 |          3 |        1 |          4 |         1 |
|          2 |          4 |        1 |          5 |         1 |
|          3 |          4 |        1 |          6 |         1 |
|          4 |          3 |        1 |          1 |         2 |
|          4 |          2 |        1 |          2 |         2 |
|          3 |          2 |        1 |          3 |         2 |
|          4 |          1 |        1 |          4 |         2 |
|          3 |          1 |        1 |          5 |         2 |
|          2 |          1 |        1 |          6 |         2 |
|          5 |          6 |        2 |          1 |         1 |
|          5 |          7 |        2 |          2 |         1 |
|          5 |          8 |        2 |          3 |         1 |
|          6 |          7 |        2 |          4 |         1 |
|          6 |          8 |        2 |          5 |         1 |
|          7 |          8 |        2 |          6 |         1 |
|          8 |          7 |        2 |          1 |         2 |
|          8 |          6 |        2 |          2 |         2 |
|          7 |          6 |        2 |          3 |         2 |
|          8 |          5 |        2 |          4 |         2 |
|          7 |          5 |        2 |          5 |         2 |
|          6 |          5 |        2 |          6 |         2 |
+------------+------------+----------+------------+-----------+