none
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

Answers

  • 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 Oakton 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 Oakton 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