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.
| Author |
Topic |
|
kamii47
Constraint Violating Yak Guru
353 Posts |
 Posted - 2008-09-05 : 16:01:39
|
| hi geeks,you guys recently helped me with a query to create a football league table. (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103143)I just wandered if you could help me finish this off?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. tblFixtureFixtureIDCompID (Division ID Name)HomeTeam (ID from tblTeam)AwayTeam (ID from tblTeam)HomeScoreAwayScoreWalkover (2, 1 or 0)tblTeamTeamIDTeamNameSQL 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.TeamNameFROM (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.TeamIDGROUP BY a.HomeTeam, tblTeam.TeamNameORDER BY Pts DESC, Agg DESC, Scored DESCKamran ShahidSr. Software Engineer(MCSD.Net,MCPD.net)www.netprosys.com |
|
|
|
|
|
|
|
Need further help in a complex query