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.
Aucun commentaire:
Enregistrer un commentaire