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.

Table/View is:

TourmamentID  INT
EventName NVARCHAR(50)
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.

1 Answers

Gordon Linoff On

You can use conditional aggregation:

select tournamentid, teamname,
       count(*) as num_completed,
       max(case when seqnum = 1 then teamname end) as team_1,
       max(case when seqnum = 2 then teamname end) as team_2,
       max(case when seqnum = 3 then teamname end) as team_3
from (select t.*,
             row_number() over (partition by tournamentid, teamname
                                order by datediff(day, teamstart, teamend)
                               ) as seqnum
      from t
      where iscomplete = 1
     ) t
group by tournamentid, teamname;