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)