Creating soccer standings table

• yulian oifa

  SSC Journeyman

  Points: 75

  More actions

  August 29, 2005 at 7:26 pm

  #363560

  Hello to all.

  I am writing a site for a funclub of specific team,and need to create a query that will generate a standings table for most available tournaments.

  That requires order by games , points , wins , goals difference , goals scored , fair play.

  Till now its easy part.

  Here is sql query :

  ****************************************

  Select teamName,teamID,games=Sum(hgames),gs=Sum(hgs),

  gr=Sum(hgr),win=Sum(hwin),draw=Sum(hdraw),

  dif=Sum(hgs)-Sum(hgr),loose=Sum(hgames)-Sum(hwin)-Sum(hdraw),

  pts=Sum(hwin)*3+Sum(hdraw),fp=Sum(hfp)/Sum(hgames) from

  ((

  Select hteam=homeTeam,hgames=Count(gameID),hgs=Sum(GoalScored),

  hgr=Sum(goalReceived),hwin=Sum(SIGN(SIGN(goalScored-goalReceived)-1)+1),

  hdraw=Sum(1-Abs(SIGN(goalScored-goalReceived))),hfp=Sum(homeFP)

  from tblgames Where tournamentID=XXX And Status>1 Group By homeTeam

  )

  UNION ALL

  (

  Select hteam=visitorTeam,hgames=Count(gameID),hgs=Sum(GoalReceived),

  hgr=Sum(goalScored),hwin=Sum(SIGN(SIGN(goalReceived-goalScored)-1)+1),

  hdraw=Sum(1-Abs(SIGN(goalScored-goalReceived))),afp=Sum(awayFP)

  from tblgames Where tournamentID=XXX And Status>1 Group By visitorTeam

  ))

  as tempTbl,tblTeams Where tblTeams.teamID=tempTbl.hteam

  group by teamID,teamName

  Order by pts desc,win desc,dif desc,gs desc,fp desc

  *************************************

  Where XXX is tournamentID ( numeric value)

  Union is done on home games calculation results and away games results calculations

  tblGames has following structure ->

  gameID,int,key ( game ID )

  tournamentID,int ( tournament ID )

  subTournamentID,int ( if it haves some phases )

  tourID,int ( tour number )

  homeTeam,int ( home team ID )

  visitorTeam,int ( visitorID )

  dateTime,datetime ( date , hour )

  Status,int ( game status : 1 started ,2 1 half .... )

  goalScored,int ( home team goals )

  goalReceived,int ( away team goals )

  homeFP,float ( home fair play coeficient )

  awayFP,float ( away fair play coeficient )

  tblTeams has following structure ->

  teamID , int , key ( team ID )

  teamName , nvarchar ( team Name )

  and some more unrelevant fields,like logo website link and so on.

  Problem starts in tournaments in which team have to be ordered by results,goal difference,away goals between teams that are equal in table.Also another problem that this specific comparison can occur in any position ( can be after points , and can be after a fair-play coeficients ).

  How do the hell can i calculate that positioning and connect it to the above query.

  Please pay attension that already the query is pretty complicated and with a lot of requests can make a troubles , even if number of raws in tblGames will not exceed 250-260 for specific tournament.

  Also will be glad to receive some suggestions how to optimize current query.

  Thanks

• September 1, 2005 at 8:00 am

  This was removed by the editor as SPAM