CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
ChrisO
ChrisO

Reputation: 5465

More elegant way of performing math on columns

Is there a more elegant way of doing this:

SELECT TOP (@NumOfGames) 
        (SUM(IIF(Fixture.HomeTeamID = @Team
            ,IIF(Fixture.Goals.FullTimeHomeGoals > Fixture.Goals.FullTimeAwayGoals
                    ,1 
                    ,IIF(Fixture.Goals.FullTimeHomeGoals < Fixture.Goals.FullTimeAwayGoals, 0, 0.5) 
                ) --IsHomeTeam
            ,IIF(Fixture.Goals.FullTimeAwayGoals > Fixture.Goals.FullTimeHomeGoals
                    ,1 
                    ,IIF(Fixture.Goals.FullTimeAwayGoals < Fixture.Goals.FullTimeHomeGoals, 0, 0.5) 
                ) --IsAwayTeam
        )) / @NumOfGames) * 100 AS Result
    FROM 
        Fixture.Fixture 
    INNER JOIN 
        Fixture.Goals ON Fixture.Goals.FixtureID  = Fixture.Fixture.FixtureID
    WHERE 
        HomeTeamID = @Team 
        OR 
        AwayTeamID = @Team

I hate the SUM section with a passion, there must be a better way of doing this.

User enters the @NumOfGames and @Team as parameters to the stored procedure. It then checks the goals table to see if a team won, drew or lost. A win is 1 point, a draw is 0.5 and a loss is 0. I want to output the sum of these numbers, divide them by the NumOfGames and multiply by 100 to get a success rate.

Upvotes: 3

Views: 174

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Yuck. Microsoft seems to have added IIF into SQL Server 2012. In any case, you might find the standard SQL way a bit more appealing. This would use the CASE statement:

SELECT TOP (@NumOfGames) 
       (SUM(case when Fixture.HomeTeamID = @Team and
                      Fixture.Goals.FullTimeHomeGoals > Fixture.Goals.FullTimeAwayGoals
                 then 1.0
                 when Fixture.HomeTeamID = @Team and
                      Fixture.Goals.FullTimeHomeGoals = Fixture.Goals.FullTimeAwayGoals
                 then 0.5
                 when Fixture.HomeTeamID = @Team
                 then 0.0
                 when Fixture.HomeTeamID <> @Team and
                      Fixture.Goals.FullTimeAwayGoals > Fixture.Goals.FullTimeHomeGoals
                 then 1.0
                 when Fixture.HomeTeamID <> @Team and
                      Fixture.Goals.FullTimeAwayGoals = Fixture.Goals.FullTimeHomeGoals
                 then 0.5
                 else 0.0
        end) / @NumOfGames) * 100 AS Result
FROM 
    Fixture.Fixture 
INNER JOIN 
    Fixture.Goals ON Fixture.Goals.FixtureID  = Fixture.Fixture.FixtureID
WHERE 
    HomeTeamID = @Team 
    OR 
    AwayTeamID = @Team

For the record, the comparisons "Fixture.HomeTeamID <> @Team" are unnecessary in the case statement and there only to make it more readable (and an iota less efficient). The case statement works by evaluating the WHEN clauses in order and then choosing the first one that matches.

Can we petition Microsoft to remove IIF() ? ;-)

Upvotes: 10

Related Questions