oakton
Starting Member
1 Posts 
Posted  04/18/2013 : 18:32:57

I'm trying to construct a view that correctly compiles standings for a youth rugby (rugby football) league.
Our Stats keeper, who has been helping me beta test everything, has discovered an anomaly with respect to how "BONUS POINTS" are being awarded by the logic of the View [MasterStandings]. It appears that a BONUS POINT is being awarded to a WINNING team for a score within seven (7) points or less of the score of the losing team. This is incorrect. The rational of the "losing" BONUS POINT is to encourage a team to keep playing hard even though the players may realize they are unlikely to win the game. Per league rules, a winning team is NOT awarded a "losing" BONUS POINT.
The league rules for awarding BONUS POINTS are:
(i) A team is awarded one (1) BONUS POINT for scoring four (4) or more "tries" in a game  regardless of winning or losing the game; (ii) A team is awarded one (1) "losing" BONUS POINT for losing by seven (7) or fewer points in a game. The winning team is NOT entitled to a losing BONUS POINT.
Thus a winning team may only be awarded one (1) BONUS POINT if it scores four (4) or more "tries"; never two BONUS POINTS.
However, a losing team can be awarded one (1) BONUS POINT for scoring four (4) or more tries and/or one (1) "losing" BONUS POINT for losing by seven (7) or fewer points in a game. Thus it is possible for a losing team to be awarded two (2) BONUS POINTS in a game.
I have been scratching my head as to how to resolve this anomaly. I'm hoping someone on the Forum can solve this problem or at least point me in the right direction.
DDLs for the two primary Tables, dbo.MatchSchedule and dbo.MatchResults, the View dbo.ResultsMaster, and the problematic View dbo.MasterStandings are appended below.
Many thanks!
Here are the DDLs.
******************************** CREATE TABLE [dbo].[MatchSchedule]( [Match_Id] [int] NOT NULL, [MatchDate] [date] NOT NULL, [MatchTime] [time](7) NULL, [Venue] [varchar](30) NULL, [Field] [varchar](20) NULL, [Conference] [varchar](20) NOT NULL, [Division] [varchar](20) NOT NULL, [District] [varchar](20) NULL, [Gender] [varchar](20) NOT NULL, [Home_Team] [varchar](30) NOT NULL, [Away_Team] [varchar](30) NOT NULL, [Referee] [varchar](30) NULL, [ACT_EMT] [varchar](50) NULL, [Notes] [varchar](50) NULL, PRIMARY KEY CLUSTERED ( [Match_Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
*******************************
CREATE TABLE [dbo].[MatchResults]( [Id] [int] NOT NULL, [MatchId] [int] NOT NULL, [HomeTries] [tinyint] NOT NULL, [HomeConv] [tinyint] NOT NULL, [HomePenalties] [tinyint] NOT NULL, [HomeDrops] [tinyint] NOT NULL, [HomeScore] AS ((([HomeTries]*(5)+[HomeConv]*(2))+[HomePenalties]*(3))+[HomeDrops]*(3)), [VisitorTries] [tinyint] NOT NULL, [VisitorConv] [tinyint] NOT NULL, [VisitorPenalties] [tinyint] NOT NULL, [VisitorDrops] [tinyint] NOT NULL, [VisitorScore] AS ((([VisitorTries]*(5)+[VisitorConv]*(2))+[VisitorPenalties]*(3))+[VisitorDrops]*(3)), [RefereeName] [varchar](50) NULL, [YellowCards] [varchar](50) NULL, [RedCards] [varchar](50) NULL, [Injury] [varchar](250) NULL, [ACT_EMT] [varchar](250) NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
***************************** create view [dbo].[ResultsMaster] as select top (1000) Match_Id, MatchDate, Conference, Home_Team, HomeTries, HomeConv, HomePenalties, HomeDrops, HomeScore, Away_Team, VisitorTries, VisitorConv, VisitorPenalties, VisitorDrops, VisitorScore, RefereeName, YellowCards, RedCards, Injury, dbo.MatchResults.ACT_EMT from dbo.MatchSchedule, dbo.MatchResults where dbo.MatchSchedule.Match_Id = dbo.MatchResults.MatchId order by dbo.MatchSchedule.Match_Id
GO
******************************
CREATE VIEW [dbo].[MasterStandings] AS WITH WhichTeams AS (SELECT 1 AS WhichTeam UNION ALL SELECT 2) SELECT TOP 1000 CASE WHEN w.WhichTeam = 1 THEN r.Home_Team ELSE r.Away_Team END As TEAM, count(*) AS PLAYED, SUM(CASE WHEN (w.WhichTeam = 1 AND r.HomeScore > r.VisitorScore) Or (w.WhichTeam = 2 AND r.HomeScore < r.VisitorScore) THEN 1 ELSE 0 END) AS WINS, SUM(CASE WHEN r.HomeScore = r.VisitorScore THEN 1 ELSE 0 END) AS TIES, SUM(CASE WHEN (w.WhichTeam = 1 AND r.HomeScore < r.VisitorScore) Or (w.WhichTeam = 2 AND r.HomeScore > r.VisitorScore) THEN 1 ELSE 0 END) AS LOSSES, SUM(CASE WHEN w.WhichTeam = 1 THEN r.HomeScore ELSE r.VisitorScore END) AS Points_FOR, SUM(CASE When w.WhichTeam = 1 THEN r.VisitorScore ELSE r.HomeScore END) AS Points_AGAINST, SUM((CASE WHEN w.WhichTeam = 1 THEN r.HomeScore ELSE r.VisitorScore END)  (CASE When w.WhichTeam = 1 THEN r.VisitorScore ELSE r.HomeScore END)) AS PD, SUM(CASE WHEN (w.WhichTeam = 1 AND r.HomeScore > r.VisitorScore) OR (w.WhichTeam = 2 AND r.HomeScore < r.VisitorScore) THEN 4 WHEN (r.HomeScore = r.VisitorScore) THEN 2 WHEN (w.WhichTeam = 1 AND r.HomeScore < r.VisitorScore) Or (w.WhichTeam = 2 AND r.HomeScore > r.VisitorScore) THEN 0 END) AS MATCH_POINTS,
SUM(CASE WHEN (w.WhichTeam = 1 AND r.HomeTries >= 4) OR (w.WhichTeam = 2 AND r.VisitorTries >= 4) THEN 1 ELSE 0 END) + SUM(CASE WHEN (w.WhichTeam = 1 AND r.HomeScore  r.VisitorScore <= 7) OR (w.WhichTeam = 2 AND r.VisitorScore  r.HomeScore <= 7) THEN 1 ELSE 0 END) AS BONUS_POINTS,
SUM(CASE WHEN (w.WhichTeam = 1 AND r.HomeScore > r.VisitorScore) OR (w.WhichTeam = 2 AND r.HomeScore < r.VisitorScore) THEN 4 WHEN (r.HomeScore = r.VisitorScore) THEN 2 WHEN (w.WhichTeam = 1 AND r.HomeScore < r.VisitorScore) Or (w.WhichTeam = 2 AND r.HomeScore > r.VisitorScore) THEN 0 END) + SUM(CASE WHEN (w.WhichTeam = 1 AND r.HomeTries >= 4) OR (w.WhichTeam = 2 AND r.VisitorTries >= 4) THEN 1 ELSE 0 END) + SUM(CASE WHEN (w.WhichTeam = 1 AND r.HomeScore  r.VisitorScore <= 7) OR (w.WhichTeam = 2 AND r.VisitorScore  r.HomeScore <= 7) THEN 1 ELSE 0 END) AS TOTAL_LEAGUE_POINTS
FROM dbo.ResultsMaster r CROSS JOIN WhichTeams w GROUP BY CASE WHEN w.WhichTeam = 1 THEN r.Home_Team ELSE r.Away_Team END ORDER BY TOTAL_LEAGUE_POINTS DESC
GO 
