I have a list of team weightings based on picking 11 players (based on their outfield positions) from each team. Below is a screenshot of the list and its code:
SELECT TeamID, SUM(PlayerWeighting) as TeamWeight
FROM ( SELECT * FROM( SELECT pl.*, ROW_NUMBER() OVER(PARTITION BY pl.TeamID, pl.Position ORDER BY NEWID()) AS Rnk FROM( SELECT DISTINCT p.PlayerID, p.Position, p.PlayerWeighting, p.FirstName, p.Suame, t.TeamID, t.TeamAbbreviation, f.WeekNumber FROM dbo.Fixture f INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID INNER JOIN dbo.Team t ON l.LeagueID = t.LeagueID INNER JOIN dbo.Player p ON t.TeamID = p.TeamID WHERE f.WeekNumber = 1) pl) po WHERE (po.position = 'GK' and po.k = 1) OR (po.position = 'DF' and po.k <= 4) OR (po.position = 'MF' and po.k <= 4) OR (po.position = 'FW' and po.k <= 2) ) as T
GROUP BY T.TeamID
What I want to do is link each team with their opposition on a week by week basis. For example if you look at the fixture list below we have team 4 vs team 60. So I want to compare the teamWeight of team 4 against the TeamWeight of team 60, the one with the highest wins (which I know can be done via CASE WHEN statement).
Then will want to compare team 14 with team 59, then team 15 with 43 and etc up to the last game for week 1 which is team 19 vs team 35.
It is should be on a week by week basis as using SQL Agent Job to run daily, so everytime the agent job executes, it will run a week of fixtures and increments to run the next week fixtures for the next time it runs. I know a parameter for week number needs to be set for this.
My question then is how to get the teams match up against each other when comparing team weightings. For this example lets say winning team (higher team weight) has 3 points inserted into home or away points in fixture table (based on if they're home or way), losing team gets 0 and if equal then 1 point each.
Thank you,