Adding an IF Statement to the SQL Statement

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
0
Cassy01
9/5/2008 2:16:35 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

7 Replies
746 Views

Similar Articles

[PageSpeed] 3

While I'm not sure I can help you directly with your problem (and I hope someone else will!), I have a general suggestion for you, for this and future problems: When you end up with a SQL problem thats beyond you (its always good to try and do it "right"...but let say you simply can't), just abuse T-SQL temporary table variables, and do the operation in multiple steps. The performance hit isn't as large as you may think (and depending on the locking strategy on the server, sometimes its actually faster, because it doesn't cause as many locks).

You should consider that...probbaly will make your code easier to maintain too.

0
shados
9/5/2008 2:44:46 PM

if thats the case can i please have some assistance on how do this?

0
Cassy01
9/5/2008 5:30:49 PM

 >if thats the case can i please have some assistance on how do this?

 Which version of SQL are you working with?

Please post a table definition file for the relevant tables


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
9/6/2008 4:17:01 PM

I am using SQL Server 2005

tblFixture
FixtureID (int, PK)
CompID (int)
HomeTeam (int)
AwayTeam (int)
HomeScore (int)
AwayScore (int)
Walkover (int)

tblTeam
TeamID (int, PK)
TeamName (vchar)

Hope this helps,

0
Cassy01
9/6/2008 4:38:19 PM

 To produce a table creation script, select the database in SSMS, locate the tables, right click and select produce script.


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
9/6/2008 6:35:33 PM

I dont have SSMS installed on my pc. I only have visual studio 2008. I can send you the ASP.net project if that helps.

My database is only local to my drive at the minute.

0
Cassy01
9/6/2008 6:58:10 PM

 If you are using SQL 2005 Express, there is a special version of SSMS at http://www.microsoft.com/Downloads/details.aspx?familyid=3C856B93-369F-4C6F-9357-C35384179543&displaylang=en


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
9/6/2008 7:49:26 PM
Reply:

Web resources about - Adding an IF Statement to the SQL Statement - asp.net.sql-datasource

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from June 2006 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

The innovation nation: Elitist statement which won't generate change
Readers discuss the Turnbull government's innovation statement.

The statements murdered grandmother Helen Dawson Key's family will never get to read
If the family members of Helen Dawson Key had their chance, they would have faced her killer and told of their pain.&nbsp;

Innovation statement: At a glance
Find out the main points from Malcolm Turnbull's innovation statement.

Obama's statement on ISIS - Business Insider Deutschland
US President Barack Obama spoke at the Pentagon on Monday to give an overview of the country's strategy to combat the terrorist group ISIS.

FOMC Statement: Fed Funds Rate target range increased to 1/4 to 1/2 percent
FOMC Statement: Information received since the Federal Open Market Committee met in October suggests that economic activity has been expanding ...

What changed in new Fed statement
This is a comparison of today's FOMC statement with the one issued after the Fed's previous policy-making meeting on Oct. 28.

Dallas Stars: Cold facts: Loss to Blues 'clearly a statement' that Stars' upcoming divisional games will ...
Blues 3, Stars 0Click here for the full box score.Three Stars1. David Backes, Blues Goal, assist and fight give St. Louis captain the Gordie... ...

Comic-Con Organizers Issue Statement After Fan Confusion
... desire to expand their annual convention in San Diego. Comic-Con International made the unusual move today of putting out a statement regarding ...

Resources last updated: 12/16/2015 8:19:47 PM