football league table (if statement amendment?)

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

Transact-SQL (2005)

football league table (if statement amendment?)

Author

Topic

cassy01
Starting Member

1 Post

Posted - 2008-09-05 : 13:23:57

I have the following tables and SQL statment which works out a league table for me from the given results.

I am now trying to add a bit more to this:

If the Walkover column = 1 then that row will be a home walkover meaning that the score will be 0(Home Score) - 0(Away Score) but the home team will recieve 3 points, and away team recieves 0 points, win column will be 1 and the played colum will be 1 as well.

If the Walkover column = 2 then that row will be a home walkover meaning that the score will be 0(Home Score) - 0(Away Score) but the away team will recieve 3 points, and home team recieves 0 points, win column will be 1 and the played colum will be 1 as well.

If the Walkover column = 0 then that row the row will be calculated as normal.

tblFixture
FixtureID
CompID (Division ID Name)
HomeTeam (ID from tblTeam)
AwayTeam (ID from tblTeam)
HomeScore
AwayScore
Walkover (2, 1 or 0)

tblTeam
TeamID
TeamName

SQL Statement:

SELECT     a.HomeTeam, SUM(a.Pld) AS Pld, SUM(a.Won) AS Won, SUM(a.Draw) AS Draw, SUM(a.Lost) AS Lost, SUM(a.Scored) AS Scored, SUM(a.Against) 
                      AS Against, SUM(a.Agg) AS Agg, SUM(a.Pts) AS Pts, tblTeam.TeamName
FROM         (SELECT     HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, 
        CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, 
       HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, 
     CASE WHEN HomeScore > AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
    FROM          tblFixtures
                       WHERE      (CompID = @CompID) AND (HomeScore IS NOT NULL)
                       UNION ALL
                       SELECT     AwayTeam, 1 AS Pld, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won, 
                                             CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost, 
                                             AwayScore AS Scored, HomeScore AS Against, AwayScore - HomeScore AS Agg, 
                                             CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
                       FROM         tblFixtures AS tblFixtures_1
                       WHERE     (CompID = @CompID) AND (HomeScore IS NOT NULL)) AS a INNER JOIN
                      tblTeam ON a.HomeTeam = tblTeam.TeamID
GROUP BY a.HomeTeam, tblTeam.TeamName
ORDER BY Pts DESC, Agg DESC, Scored DESC