dimanche 12 juin 2016

Using DATEADD() within a cte

I'm trying to use a DATEADD() function in my code where for every 'week_number_ we apply a date and then add 7 days for the next week number. The issues I've had is that if I try this code:

DATEADD(day,(ROW_NUMBER() OVER (ORDER BY LeagueID)-1)*7,@StartFixtureWeek) AS FixtureDate

It displays new dates for every row, and as each week has six rows assigned to them (the number of rows assigned could change in the future), instead of each row stating 'week_number' 1, and FixtureDate '01-09-2016', it states that first row of 'week_number' 1 has FixtureDate '01-09-2016', then second row of 'week_number1' has FixtureDate '08-09-2016' and so on.

It should be all FixtureDate for 'week_number' 1 is '01-09-2016', then all of 'week_number' 2 is '08-09-2016, then 'week_number' 3 is '15-09-2016' and so on.

If I try this:

DATEADD(day,(ROW_NUMBER() OVER (ORDER BY LeagueID, week_number)-1)*7,@StartFixtureWeek) AS FixtureDate,

No results are displayed

Below is the code I currently have with the DATEADD() function applied (14th line from bottom. Virtually I need it working exactly how my 'Week_number' works but for FixtureDate instead.

UPDATE:

   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
        -- if this is an odd number then generate a fake team that's 0.
        SELECT TeamID AS id,
            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
        -- Insert a fake team if required
        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 AS num_teams,
            num_fake AS 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,
           DATEADD(day,(ROW_NUMBER() OVER (ORDER BY week_number)-1)*7,@StartFixtureWeek) AS WeekNumber,
           h.id,
           a.id,
           c.leagueid
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY LeagueID, week_number)  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;

    select * from dbo.Fixture
    where LeagueID = 1  

SCHEMA:

League:

[LeagueID] TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
[LeagueName] VARCHAR(30) UNIQUE

Team:

[TeamID] TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
[TeamAbbreviation] CHAR(3) UNIQUE, 
[TeamName] VARCHAR(50) UNIQUE, 
[LeagueID] TINYINT CONSTRAINT FK_Team_League FOREIGN KEY REFERENCES League(LeagueID) 

Fixture:

[FixtureID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[WeekNumber] INT NOT NULL,
[FixtureDate] DATE NULL,
[HomeTeamID] TINYINT NULL,
[AwayTeamID] TINYINT NULL,
[LeagueID] TINYINT CONSTRAINT FK_Fixture_League FOREIGN KEY REFERENCES League(LeagueID)

DATA:

League:

1, 'English Premiership'
2, 'English Division 1'

Teams:

1, 'BCN', 'FC Barcelona', 1
2, 'MAD', 'Real Madrid', 1
3, 'ATH', 'Athletico Madrid', 1
4, 'ESP', 'Espanyol', 1
5, 'MAN', 'Manchester United', 2
6, 'BOL', 'Bolton', 2
7, 'CHE', 'Chelsea', 2
8, 'ARS', 'Arsenal', 2

Below is current output of 'Fixture' table and as you can see the dates are incorrect as that it gives different dates for a single week when obviously they should be the same date per week.

enter image description here

Aucun commentaire:

Enregistrer un commentaire