# League Standings - Further Question

### Question

• First, I'd like to thank those who offered help and advice on my request for help earlier in the week; viz post of Tuesday, April 16, 2013 as "League Standings".

Secondly, apologies if by this posting I am not following established protocol by here posting a variation of the earlier query as  "League Standings - Further Question".

I thought the problem had been solved; however, testing the solution with dummy data has thrown up an anomaly.  I have tried several different code formulations/iterations but so far none have fixed the problem.  I am therefore hoping that greater minds can point out the

logical flaw and maybe point me in the correct direction.

The problem relates to the section of the View [dbo].[ResultsMaster] that calculates & allocates "Bonus Points", specifically the part that calulates & allocates LOSING Bonus Points.

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.

```SUM(CASE WHEN
(w.WhichTeam = 1 AND r.HomeTries >= 4) OR (w.WhichTeam = 2 AND r.VisitorTries >= 4) THEN 1 ELSE 0 END)  ---This works per the league rule for four tries---
+
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)  ---This section does NOT work correctly.  It allocates a LOSING Bonus Point even when the score differential is greate than

seven (7) points.  As noted above, I have tried several iterations but to no avail.-----
AS BONUS_POINTS,```
All the DDls are here posted below.
```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```
Lastly the problematic VIEW [dbo].[MasterStandings]

```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,

---BEGIN BONUS POINT SECTION---

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,

---END BONUS POINT SECTION---

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```
Many, many thanks!

Oakton

Saturday, April 20, 2013 4:32 PM

• First, I note that there are TOP 1000 and ORDER BY in the view. Since I don't think there are 1000 teams in the leauge that looks funny. I suspect that you have put that in because you want the view to be ordered, but a view is by definition an unordered object, and the only way to get an ordered result from a view is to apply an ORDER BY clause when you query it. If you want a database object that encloses the sort order, you need to use a stored procedure.

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)  ---This section does NOT work correctly.  It allocates a LOSING Bonus Point even when the score differential is greate than

I don't know exactly how this WhichTeam works, but I guess that

(w.WhichTeam = 1 AND r.HomeScore - r.VisitorScore <= 7)

is supposed to award a losing point to the home team if the lose by less than seven points. In that case the condition should be:

(w.WhichTeam = 1 AND r.HomeScore - r.VisitorScore BETWEEN -7 AND -1) OR
(w.WhichTeam = 2 AND r.VisitorScore - r.HomeScore BETWEEN -7 AND -1)

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Marked as answer by Monday, April 22, 2013 3:43 PM
Saturday, April 20, 2013 6:56 PM

### All replies

• First, I note that there are TOP 1000 and ORDER BY in the view. Since I don't think there are 1000 teams in the leauge that looks funny. I suspect that you have put that in because you want the view to be ordered, but a view is by definition an unordered object, and the only way to get an ordered result from a view is to apply an ORDER BY clause when you query it. If you want a database object that encloses the sort order, you need to use a stored procedure.

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)  ---This section does NOT work correctly.  It allocates a LOSING Bonus Point even when the score differential is greate than

I don't know exactly how this WhichTeam works, but I guess that

(w.WhichTeam = 1 AND r.HomeScore - r.VisitorScore <= 7)

is supposed to award a losing point to the home team if the lose by less than seven points. In that case the condition should be:

(w.WhichTeam = 1 AND r.HomeScore - r.VisitorScore BETWEEN -7 AND -1) OR
(w.WhichTeam = 2 AND r.VisitorScore - r.HomeScore BETWEEN -7 AND -1)

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Marked as answer by Monday, April 22, 2013 3:43 PM
Saturday, April 20, 2013 6:56 PM
• Erland,

Thank you!  Your suggestion of using BETWEEN -7 AND -1 rather than <= to allocate a losing Bonus Point was right on the money!  We ran some dummy data through last night and checked carefully that the calculation and allocation of points was correct.  It was all correct.

Nothing like a second pair (of experienced) eyes to spot the flaw and suggest a good solution.

Many thanks!

Oakton

Monday, April 22, 2013 3:48 PM