Trying to do a lot in one query. Not sure if I should be doing distinct sub queries or a group of joins.
This is running on a SQL Database App on Microsoft Azure.
TourmamentID INT EventID INT EventName NVARCHAR(50) TeamID INT TeamName NVARCHAR(50) EventStart Datetime TeamEnd Datetime iscomplete bit
Here is example data from the view.
TournamentID, EventName, TeamName, EventStart, TeamEnd, iscomplete ---------- -------- ---------- ------- --------- ------- 1 E1 T1 sqldate sqldate 1 1 E2 T1 sqldate sqldate 1 1 E1 T2 sqldate sqldate 1 1 E2 T2 sqldate sqldate 1 1 E1 T3 sqldate sqldate 1 2 E1 T1 sqldate null null 2 E1 T2 sqldate sqldate 1 2 E2 T2 sqldate sqldate 1 3 E1 T3 sqldate null null
I need to show a standings result set that would have the total number of teams who have completed an event with the top three fastest times as columns. Having the team name and time would be best.
EventName, NumberTeamscompleted, 1st 2nd 3rd E1 3 Datediff (T1) DateDiff (T3) DateDiff(T2) E2 2
Query would only return result for a single tournament (where
tournamentID = 1)
I have done a self join back on the table to get more than one set of columns for results but I have not gotten the ability to have the second set be the second fastest per event.
I also have an independent query that does a count and group by, order by to get the sum but when I try to merge them everything falls apart.