August 19, 2014 at 3:00 pm
Excel Formula =+((+MAX(B2:B9)-B2)+(+(C2-(MIN(C2:C9)))))/2
Can this be converted to SQL?
Excel Table Data
Column.....A.......B.........C.............D......
Row1....Team...Wins...Losses...GamesBack
Row2........A.......6.........0.............0.......
Row3........A.......6.........0.............0.......
Row4........A.......4.........2.............2.......
Row5........A.......3.........2.............2.5....
Row6........A.......3.........3.............3.......
Row7........A.......3.........3.............3.......
Row8........A.......3.........3.............3.......
Row9........A.......1.........5.............5.......
August 19, 2014 at 3:13 pm
Should be doable, but first I need to know why you're subtracting B2 and subtracting from C2? Why not B3 and C3, or B9 and C9? What is special about row 2?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 19, 2014 at 3:33 pm
I think this should do the trick, however I am assuming that the MIN and MAX functions are fixed to the entire range.
WITH ExcelData AS (
SELECT *
FROM (VALUES
('A',6,0),
('A',6,0),
('A',4,2),
('A',3,2),
('A',3,3),
('A',3,3),
('A',3,3),
('A',1,5)
) EXCEL(Team, Wins, Losses)
)
SELECT Team, Wins, Losses,
(ABS((MAX(Wins) OVER (PARTITION BY (SELECT NULL)) - Wins)) +
(ABS(Losses - MIN(Losses) OVER (PARTITION BY (SELECT NULL))))) / 2.0
FROM ExcelData
ORDER BY Wins DESC, Losses
Edit: After looking at what I suspect you are trying to achieve I replaced the PARTITION BY Team clause.
August 19, 2014 at 3:58 pm
I have attached the excel file.
The standings is not a true table...it looks to the "games" table and the standings are all calculated and sorted.
So I have created the table in sequel where all the games will continue to be populated so the sql can run against the games table.
August 19, 2014 at 3:59 pm
I have attached the excel file.
The standings is not a true table...it looks to the "games" table and the standings are all calculated and sorted.
So I have created the table in sequel where all the games will continue to be populated so the sql can run against the games table.
August 19, 2014 at 4:32 pm
davidmckee (8/19/2014)
I have attached the excel file.The standings is not a true table...it looks to the "games" table and the standings are all calculated and sorted.
So I have created the table in sequel where all the games will continue to be populated so the sql can run against the games table.
In that case 🙂 the following view should be what you are after
CREATE View Standings AS
WITH Wins AS (
SELECT Winner, COUNT(*) Wins
FROM Games
GROUP BY Winner
),
Losses AS (
SELECT Loser, COUNT(*) Losses
FROM Games
GROUP BY Loser
),
WinLoss AS (
SELECT COALESCE(Winner, Loser) Team, ISNULL(Wins,0) Wins, ISNULL(Losses,0) Losses
FROM Wins FULL OUTER JOIN Losses ON Winner = Loser
)
SELECT Team, Wins, Losses,
CAST(Case WHEN Wins + Losses = 0 THEN 0.0 ELSE Wins / CAST(Wins + Losses AS Decimal(6,3)) END AS Decimal(6,3)) Percentage,
(ABS((MAX(Wins) OVER (PARTITION BY (SELECT NULL)) - Wins)) +
(ABS(Losses - MIN(Losses) OVER (PARTITION BY (SELECT NULL))))) / 2.0 GamesBehind
FROM WinLoss
August 19, 2014 at 5:33 pm
I was looking at the SQL...question how do winner/loser to the sql table so when the score is entered it updates the Winner/Loser column? Or is there a better way?
I guess I don't need the winner/loser column in the table if the SQL can sum the wins and loses?
August 19, 2014 at 5:40 pm
I would go for computed columns for the winner/loser
Something like this:
CREATE TABLE Games (
HomeTeam CHAR(1) NOT NULL, -- use an appropriate varchar if you have full names
AwayTeam CHAR(1) NOT NULL, -- use an appropriate varchar if you have full names
HomeScore INT NOT NULL,
AwayScore INT NOT NULL,
Winner AS CASE WHEN HomeScore > AwayScore THEN HomeTeam ELSE AwayTeam END PERSISTED,
Loser AS CASE WHEN HomeScore < AwayScore THEN HomeTeam ELSE AwayTeam END PERSISTED
)
One question though, how do you handle draws?
August 19, 2014 at 5:50 pm
I appreciate all your help...there are no Ties (only wins and loses)
Sorry this may be a terrible question but how do I add the fields to make them calculate? When I say add a column to the table it gives me these options
I logged into the web server and clicked add column it asks for
1.Name
2.Type INT is highlighted
3.Length
4.Default None is highighted
5.Collation
6.Attributes
7.Null is not checked
8.Index --
9. A_I is not checked
10. Comments
Then is asks me to save? where do I put the formula below?
CREATE TABLE Games (
HomeTeam CHAR(1) NOT NULL, -- use an appropriate varchar if you have full names
AwayTeam CHAR(1) NOT NULL, -- use an appropriate varchar if you have full names
HomeScore INT NOT NULL,
AwayScore INT NOT NULL,
Winner AS CASE WHEN HomeScore > AwayScore THEN HomeTeam ELSE AwayTeam END PERSISTED,
Loser AS CASE WHEN HomeScore < AwayScore THEN HomeTeam ELSE AwayTeam END PERSISTED
)
August 19, 2014 at 5:58 pm
I'm not sure what tool you are using to connect to the database, so I can't give you any help on that. The statement I provided is to create the complete (empty) table.
I'm offline for a while now, but I'll check in on this later.
August 19, 2014 at 6:12 pm
I connect to the server/table using phpMyAdmin I believe it is a MySQL database
The menu gives me the following options...
Browse
Structure
SQL
Search
Insert
Export
Import
Operations
Triggers
Thanks in advance if you have time later
Cheers,
David
August 19, 2014 at 8:12 pm
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)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2014 at 8:53 pm
This is the exact result...except it seems to be in MSSql...the web server runs MySql
I did some research after copying the code into the query and receiving errors...maybe this is MS SQL and not MySql
Does anyone know how to convert this from MSSQL to MySql?
Again assuming this is the difference....
This was the error when I run it
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH
cteWinLose AS
( --=== Calculates the wins/losses using a CROSS APPLY to "' at line 1
August 19, 2014 at 9:19 pm
davidmckee (8/19/2014)
This is the exact result...except it seems to be in MSSql...the web server runs MySqlI did some research after copying the code into the query and receiving errors...maybe this is MS SQL and not MySql
Does anyone know how to convert this from MSSQL to MySql?
Again assuming this is the difference....
This was the error when I run it
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH
cteWinLose AS
( --=== Calculates the wins/losses using a CROSS APPLY to "' at line 1
It would appear that you need to convert the CTEs (the WITHs) to FROMs as "derived tables".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2014 at 10:49 pm
Jeff Moden (8/19/2014)
davidmckee (8/19/2014)
This is the exact result...except it seems to be in MSSql...the web server runs MySqlI did some research after copying the code into the query and receiving errors...maybe this is MS SQL and not MySql
Does anyone know how to convert this from MSSQL to MySql?
Again assuming this is the difference....
This was the error when I run it
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH
cteWinLose AS
( --=== Calculates the wins/losses using a CROSS APPLY to "' at line 1
It would appear that you need to convert the CTEs (the WITHs) to FROMs as "derived tables".
I've had a bit of a play with this in SQLFiddle and found a couple of views worked well for me. Here's what I came up with, thanks to Jeff for the setup.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply