Home Forums SQL Server 2008 SQL Server Newbies Convert Excel formula to SQL RE: Convert Excel formula to SQL<!-- 864 -->

  • davidmckee (8/20/2014)


    Here you go!

    Final MySQL:

    SELECT Team, Wins, Losses, CAST(IF(Wins + Losses = 0,0.0,Wins / CAST(Wins + Losses AS Decimal(6,3))) AS Decimal(6,3)) Percentage, ((ABS((SELECT MAX(Wins ) FROM WinLossSum) - Wins )) + (ABS(Losses - (SELECT MIN(Losses) FROM WinLossSum)))) / 2.0 AS GamesBehind

    FROM WinLossSum

    ORDER BY `Percentage` DESC,`Wins` DESC,`Losses` ASC

    Results

    TEAM......WINS......LOSSES......PERCENTAGE......GAMESBEHIND

    ..A............6..............0................1......................0.........

    ..B............6..............0................1......................0.........

    ..C............4..............2................0.667................2.........

    ..D............3..............2................0.6...................2.5.......

    ..E............3..............3................0.5...................3.........

    ..F............3..............3................0.5...................3.........

    ..G............3..............3................0.5...................3.........

    ..H............1..............5................0.167................5.........

    ..I.............0..............5................0......................5.5......

    ..J.............0..............6................0......................6........

    Thanks, David. What's the code for the WinLossSum view look like?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)