sports league standings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

sports league standings

Author

Topic

oakton
Starting Member

3 Posts

Posted - 2013-04-18 : 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

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2013-04-22 : 11:37:35

I think all you're missing is a check on the 2nd part of the BONUS_POINTS to make sure that bonus point is only awarded if the team loses.

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 AND r.HomeScore - r.VisitorScore <= 7)
OR (w.WhichTeam = 2 AND r.VisitorScore < r.HomeScore AND r.VisitorScore - r.HomeScore <= 7) THEN 1 ELSE 0 END) AS BONUS_POINTS,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources