A little help with Soccer League please

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 2008 Forums

Transact-SQL (2008)

A little help with Soccer League please

Author

Topic

jkurzner
Starting Member

8 Posts

Posted - 2012-04-30 : 10:11:29

I have a need to calculate soccer tables ("standings") for teams participating in a soccer league.

I have been able to create the tables using total points as my ranking of teams with ties being broken by a series of tie breakers including score differntial, etc. My problem is that I need a way to compare head to head results as a tie breaking parameter.

Here's a simplified structure and the current query that ranks the divisions


CREATE TABLE [dbo].[SCHEDULE](
	[GNUM] [float] NULL,
	[DIVISION] [nvarchar](255) NULL,
	[FCODE] [nvarchar](255) NULL,
	[GDATE] [datetime] NULL,
	[HTEAM] [nvarchar](255) NULL,
	[VTEAM] [nvarchar](255) NULL,
	[PLAYED] [bit] NULL,
	[HRUNS] [float] NULL,
	[VRUNS] [float] NULL,
	[HFORFEIT] [bit] NULL,
	[VFORFEIT] [bit] NULL,
	[HCLUB] [nvarchar](255) NULL,
	[VCLUB] [nvarchar](255) NULL,
) ON [PRIMARY]

GO

Presently there are two views that calculate the points because we allow for a maximum goal differential per game and also assign points to wins losses and ties. It's points that is the first ranking, then head-to-head, then maximum goal differential, etc.


SELECT     *, CASE WHEN GD > 3 THEN 3 WHEN GD < - 3 THEN - 3 ELSE GD END AS [MaxGD], CASE WHEN W = 1 THEN 3 WHEN D = 1 THEN 1 ELSE 0 END AS [Points]
FROM         (SELECT     SCHEDULE.GNUM, SCHEDULE.DIVISION, SCHEDULE.HTEAM AS TeamID, CASE WHEN (Schedule.HRuns > Schedule.VRuns) OR
                                              (Schedule.VForfeit = 1) THEN 1 ELSE 0 END AS [W], CASE WHEN (Schedule.HRuns < Schedule.VRuns) AND (Schedule.VForfeit = 0) 
                                              THEN 1 ELSE 0 END AS [L], CASE WHEN Schedule.HRuns = Schedule.VRuns THEN 1 ELSE 0 END AS [D], 
                                              CASE WHEN Schedule.Hforfeit = 1 THEN 1 ELSE 0 END AS [Forfeit], Schedule.HRuns AS [GF], Schedule.VRuns AS [GA], 
                                              Schedule.HRuns - Schedule.VRuns AS [GD], Teams.TCode, Teams.Team
                       FROM          SCHEDULE INNER JOIN
                                              TEAMS ON SCHEDULE.HTEAM = TEAMS.TCODE
                       WHERE      (SCHEDULE.PLAYED <> 0) AND (SCHEDULE.HFORFEIT = 0)) AS T
UNION
SELECT     *, CASE WHEN GD > 3 THEN 3 WHEN GD < - 3 THEN - 3 ELSE GD END AS [MaxGD], CASE WHEN W = 1 THEN 3 WHEN D = 1 THEN 1 ELSE 0 END AS [Points]
FROM         (SELECT     SCHEDULE.GNUM, SCHEDULE.DIVISION, SCHEDULE.VTEAM AS TeamID, CASE WHEN (Schedule.HRuns < Schedule.VRuns) OR
                                              (Schedule.HForfeit = 1) THEN 1 ELSE 0 END AS [W], CASE WHEN (Schedule.HRuns > Schedule.VRuns) AND (Schedule.HForfeit = 0) 
                                              THEN 1 ELSE 0 END AS [L], CASE WHEN Schedule.HRuns = Schedule.VRuns THEN 1 ELSE 0 END AS [D], 
                                              CASE WHEN Schedule.Hforfeit = 1 THEN 1 ELSE 0 END AS [Forfeit], Schedule.VRuns AS [GF], Schedule.HRuns AS [GA], 
                                              Schedule.VRuns - Schedule.HRuns AS [GD], Teams.TCode, Teams.Team
                       FROM          SCHEDULE, teams
                       WHERE      (Schedule.VTeam = Teams.TCode) AND (SCHEDULE.PLAYED <> 0) AND (SCHEDULE.VFORFEIT = 0)) AS T
UNION
SELECT     *, CASE WHEN GD > 3 THEN 3 WHEN GD < - 3 THEN - 3 ELSE GD END AS [MaxGD], 0 AS [Points]
FROM         (SELECT     Schedule.gnum, Schedule.Division, Schedule.HTeam AS TeamID, 0 AS [W], 1 AS [L], 0 AS [D], 1 AS [Forfeit], 0 AS [GF], 3 AS [GA], - 3 AS [GD], Teams.TCode, 
                                              Teams.Team
                       FROM          Schedule, Teams
                       WHERE      Schedule.HTeam = Teams.TCode AND Schedule.Played <> 0 AND HForfeit = 1) AS T
UNION
SELECT     *, CASE WHEN GD > 3 THEN 3 WHEN GD < - 3 THEN - 3 ELSE GD END AS [MaxGD], 0 AS [Points]
FROM         (SELECT     Schedule.gnum, Schedule.Division, Schedule.VTeam AS TeamID, 0 AS [W], 1 AS [L], 0 AS [D], 1 AS [Forfeit], 0 AS [GF], 3 AS [GA], - 3 AS [GD], Teams.TCode, 
                                              Teams.Team
                       FROM          Schedule, Teams
                       WHERE      Schedule.VTeam = Teams.TCode AND Schedule.Played <> 0 AND VForfeit = 1) AS T


And here is the standings query which runs agains tht points query for creating the table views

SELECT     TOP (100) PERCENT DIVISION, TCode, Team, Wins, Losses, Draws, Forfeits, Points, GF, GA, MaxGD, Wins + Losses + Draws AS TotGms, 
                      CAST(Points / (Wins + Losses + Draws) AS decimal(6, 4)) AS AvgPts, CAST(MaxGD / (Wins + Losses + Draws) AS decimal(6, 4)) AS AvgGD
FROM         (SELECT DISTINCT 
                                              TOP (100) PERCENT DIVISION, TCode, Team, SUM(W) AS Wins, SUM(L) AS Losses, SUM(D) AS Draws, SUM(Forfeit) AS Forfeits, CAST(SUM(Points) 
                                              AS decimal(5, 0)) AS Points, SUM(GF) AS GF, SUM(GA) AS GA, CAST(SUM(MaxGD) AS decimal(5, 0)) AS MaxGD
                       FROM          dbo.PointsQuery
                       GROUP BY DIVISION, TCode, Team
                       ORDER BY DIVISION, Points DESC, MaxGD DESC) AS x
ORDER BY DIVISION, AvgPts DESC, AvgGD DESC

Tables names are schedule, teams, clubs, division. Clubs have multiple teams and teams participate in divisions. Divisions basically hold the name of the division and the points for wins, ties, etc. Therefore it is possible for each division to have different rules for ranking the standings. Right now the rules are all the same for the competitive divisions with 3 points for a win, 1 for a draw and 0 for a loss. In addition there is also the ability to record a forfeit to both teams (i.e. bad behavior) where both teams lose. There is a maximum goal differential of + or - 3 for each game. Therefore, if a team wins 8-0 they only get a +3 goal differential for that game and the loser only gets a -3. The sum of the goal differentials is a tie-breaking component. As far as head-to-head; that tie-breaker comes immediately after the average points ranking. We use average points to rank the teams because sometimes they don't all play the same number of games.

Tie-breakers in order is:
Average Points
Head-to-Head
Avg Goal Differential
Most wins
Fewest losses
Fewest goals allowed

I just can't get my head around a way to do the head-to-head comparison when teams have a tie in points. Also, for head-to-head when multiple teams are tied.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 10:14:30

please post some data and explain the points concept rather than posting the query. then explain what output you want to get

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkurzner
Starting Member

8 Posts

Posted - 2012-04-30 : 12:11:42

Good points. Consider the following


Team	Wins	Losses	Draws	Points	AvgPts	AvgGD
Team A	11	1	0	33	2.75	1.917
Team B	11	1	0	33	2.75	1.833
Team C	9	2	1	28	2.333	1.75
Team D	8	3	1	25	2.083	1.333
Team E	7	4	1	22	1.833	0.667
Team F	6	3	3	21	1.75	0.917
Team G	5	4	3	18	1.5	0.25
						
In the Above Example consider that Team B beat Team A in a head to head match and that was the only time they played each other.						
The order should be Team B then Team A						
						
Team	Wins	Losses	Draws	Points	AvgPts	AvgGD
Team A	10	2	0	30	3	1.917
Team B	10	2	0	30	3	1.833
Team C	10	2	0	30	3	1.75
Team D	8	3	1	25	2.083	1.333
Team E	7	4	1	22	1.833	0.667
Team F	6	3	3	21	1.75	0.917
Team G	5	4	3	18	1.5	0.25
						
						
In the above example Team A lost to both team B and team C.  Team C beat team B.						
The order should be 						
Team C						
Team B						
Team A

The existing ranking is due to average points followed by average goal differential. I need head-to-head ranking to occur before the goal differential ranking.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-30 : 12:48:51

you seem to be missing goals for and goals against. do you have that data
I am not sure if little league rules are different that the standard rules but tie breakers are resolved via goals for and goals against differential, are they not?
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-30 : 13:05:17

declare @rankings table(Team varchar(255), Wins int, Losses int, Draws int, points int, AvgPts decimal(10,3), AvgGD decimal(10,3))

insert into @rankings
select 'Team A', 11, 1, 0, 33, 2.75, 1.917
union
select 'Team B', 11, 1, 0, 33, 2.75, 1.833
union
select 'Team C', 9, 2, 1, 28, 2.333, 1.75
union
select 'Team D', 8, 3, 1, 25, 2.083, 1.333
union
select 'Team E', 7, 4, 1, 22, 1.833, 0.667
union
select 'Team F', 6, 3, 3, 21, 1.75, 0.917
union
select 'Team G', 5, 4, 3, 18, 1.5, 0.25

declare @gameswonlost table(WinnerTeam varchar(255), LoserTeam varchar(255))
insert into @gameswonlost
select 'Team C', 'Team A'
union
select 'Team C', 'Team A'
union
select 'Team C', 'Team B'

select * from @rankings

select a.Team, COUNT(b.WinnerTeam) as winnerCount, AvgPts
from @rankings a
left join @gameswonlost b
on a.Team = b.WinnerTeam
group by a.Team,b.WinnerTeam, AvgPts
ORDER BY COUNT(b.WinnerTeam) DESC

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jkurzner
Starting Member

8 Posts

Posted - 2012-05-01 : 10:56:30

The goals for and goals against don't really matter for the purpose of tie breaking head-to-head since that fell below average goal differential and head-to-head. I left them out for the purpose of this example.

In other words, unless head to head and average goal differential were the same, goals for or against would not be considered. Also, the league that I work with only allows a maximum goal differential of +3 or -3 per game and head-to-head is the second tie breaker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources