Convert Excel formula to SQL

Question

Post reply

Convert Excel formula to SQL

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

davidmckee Right there with Babe Points: 719 More actions · Answer 1

This is very close but something else is slightly off...the third place team should be 2 games behind not 3

This was the SQLfiddle statement:

(ABS((SELECT MAX(Wins ) FROM WinLossSum) - Wins )) +

(ABS(Losses - (SELECT MIN(Losses) FROM WinLossSum))) / 2.0 AS GamesBehind

(6-4) + (2-0) / 2

2+2/2=2 not 3

The Result should be 2 so I am not sure how it is coming to 3?

davidmckee Right there with Babe Points: 719 More actions · Answer 2

Actually I got it!!!

I adjusted the SQLfiddle statement needed a couple extra brackets to complete the addition before dividing by 2:

This now works!!!

((ABS((SELECT MAX(Wins ) FROM WinLossSum) - Wins )) +

(ABS(Losses - (SELECT MIN(Losses) FROM WinLossSum)))) / 2.0 AS GamesBehind

(6-4) + (2-0) / 2

2+2/2=2

You all are awesome!! Thanks so much for this!

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

Our pleasure. Evven though it's MySQL, any chance of seeing the final code you ended up with including the views? Thanks.

--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)

davidmckee Right there with Babe Points: 719 More actions · Answer 4

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........

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

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)

davidmckee Right there with Babe Points: 719 More actions · Answer 6

I did not change the code for the WinLossSum. Thanks again!

CREATE VIEW WinLossSum AS

SELECT Team, SUM(Result) Wins, SUM(Result^1) Losses

FROM WinLoss

GROUP BY Team

Results

TEAM......WINS......LOSSES

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

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

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

..D............ 3..............2...

..E............ 3..............3...

..F............ 3..............3...

..G............ 3..............3...

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

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

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 7

Thanks, David. I appreciate it.

--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)

sqldriver SSChampion Points: 10440 More actions · Answer 8

Jeff Moden (8/19/2014)
davidmckee (8/19/2014)
I connect to the server/table using phpMyAdmin[font="Arial Black"] I believe it is a MySQL database[/font]
Wellllll.... crud. I came up with something fun but now I don't know if it'll work for you. I'll still post it anyway...
Here's the test data if anyone wants to "play"...
SELECT HomeTeam, HomeScore, AwayTeam, AwayScore
INTO #Games
FROM (
SELECT 'B',2,'D',0 UNION ALL
SELECT 'G',17,'D',71 UNION ALL
SELECT 'I',25,'F',46 UNION ALL
SELECT 'E',26,'D',38 UNION ALL
SELECT 'I',26,'G',41 UNION ALL
SELECT 'H',26,'E',54 UNION ALL
SELECT 'J',33,'F',51 UNION ALL
SELECT 'E',34,'G',28 UNION ALL
SELECT 'J',34,'G',35 UNION ALL
SELECT 'E',35,'F',45 UNION ALL
SELECT 'E',37,'A',42 UNION ALL
SELECT 'C',39,'J',29 UNION ALL
SELECT 'B',41,'F',16 UNION ALL
SELECT 'H',42,'G',45 UNION ALL
SELECT 'D',43,'F',20 UNION ALL
SELECT 'A',44,'F',41 UNION ALL
SELECT 'E',46,'J',27 UNION ALL
SELECT 'I',47,'H',63 UNION ALL
SELECT 'C',50,'B',57 UNION ALL
SELECT 'C',50,'G',33 UNION ALL
SELECT 'A',51,'D',48 UNION ALL
SELECT 'B',52,'J',26 UNION ALL
SELECT 'B',57,'H',29 UNION ALL
SELECT 'A',58,'J',14 UNION ALL
SELECT 'A',61,'C',24 UNION ALL
SELECT 'C',61,'H',47 UNION ALL
SELECT 'C',62,'I',37 UNION ALL
SELECT 'B',66,'I',20 UNION ALL
SELECT 'A',75,'H',21
) d (HomeTeam, HomeScore, AwayTeam, AwayScore)
;
Notice that the test data does NOT have a Winner/Loser column, calculated or otherwise.
Here's the code I was getting ready to post and then saw the "MySQL" thingy... dunno if it'll still work for you or not.
WITH
cteWinLose AS
( --=== Calculates the wins/losses using a CROSS APPLY to "unpivot" the data.
-- Means we only need to make a single table scan and there are no JOINs.
SELECT ca.Team
,Wins = SUM(ca.Win)
,Losses = SUM(CASE WHEN ca.Win = 0 THEN 1 ELSE 0 END)
FROM #Games
CROSS APPLY (
SELECT HomeTeam, CASE WHEN Homescore >= AwayScore THEN 1 ELSE 0 END UNION ALL
SELECT AwayTeam, CASE WHEN AwayScore >= Homescore THEN 1 ELSE 0 END
) ca (Team,Win)
GROUP BY ca.Team
)
,cteCalcs AS
( --=== Once we know the wins/losses, we can calculate the win average and Games Behind
SELECT *
,PCT = CAST(Wins/(Wins+Losses+0.0) AS DECIMAL(6,3))
,GamesBehind = CAST(
( MAX(Wins) OVER (PARTITION BY (SELECT NULL)) - Wins
+ Losses - MIN(Losses) OVER (PARTITION BY (SELECT NULL))
) / 2.0
AS DECIMAL(6,3))
FROM cteWinLose
) --=== Last but not least, calculation the position of each team and display all
SELECT Place = DENSE_RANK() OVER (ORDER BY GamesBehind),*
FROM cteCalcs
ORDER BY Place, Team
;
Here's the output...
Place Team Wins Losses PCT GamesBehind
----- ---- ---- ------ ----- -----------
1 A 6 0 1.000 0.000
1 B 6 0 1.000 0.000
2 C 4 2 0.667 2.000
3 D 3 2 0.600 2.500
4 E 3 3 0.500 3.000
4 F 3 3 0.500 3.000
4 G 3 3 0.500 3.000
5 H 1 5 0.167 5.000
6 I 0 5 0.000 5.500
7 J 0 6 0.000 6.000
(10 row(s) affected)

How did you find wrapping the CASE in your APPLY performed? Whenever I've tried it, I've had really bad luck vs. a CASE not wrapped in an APPLY. I was actually close to posting a question about it recently, but the business use for the query was canceled.

Thanks