SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 sports league standings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oakton
Starting Member

1 Posts

Posted - 04/18/2013 :  18:32:57  Show Profile  Reply with Quote
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

USA
292 Posts

Posted - 04/22/2013 :  11:37:35  Show Profile  Reply with Quote
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,
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000