Post reply

Team Scdule with Round Robin

  • December 30, 2014 at 2:16 am

    #314172

    Hi to all

    i have to schedule 10 team within expected date range Like 01/01/2015 to 10/01/2015 (dd/mm/yyyy). with the use of round robin algorithm.

    1) team-1 must play only once with remaining teams

    2) one pair must come once.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[tbl_Team](

    [Id] [int] NOT NULL,

    [TeamName] [nvarchar](50) NOT NULL

    ) ON [PRIMARY]

    GO

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (1, N'Team-1');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (2, N'Team-2');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (3, N'Team-3');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (4, N'Team-4');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (5, N'Team-5');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (6, N'Team-6');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (7, N'Team-7');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (8, N'Team-8');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (9, N'Team-9');

    INSERT [dbo].[tbl_Team] ([Id], [TeamName]) VALUES (10, N'Team-10');

  • December 30, 2014 at 2:47 am

    #1766949

    SELECT

    b.*,

    a.*

    FROM #tbl_Team a

    INNER JOIN #tbl_Team b ON b.id < a.id

    ORDER BY b.id, a.id

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply