I had to write a SQL query recently to do a few joins and sums, and at the end of it I realized that I have nearly written a storybook. I know the tools to optimize it, but my trouble is the length of it. I am very sure, it can be shortened up, but my little brain refuses to strengthen this belief.
Basically, I want a list of teams ordered by the total Distance + Bonus. The Bonus consists of the team bonus and the bonus of team members (Member Bonus). In order to calculate distance covered by the team, I have to sum up the distance covered by the team members.
The relationship between the tables:
SELECT TeamID, Name, Country, Region, OfficeLocation AS [Office Location], Minutes As Steps, Distance, SUM(MemberBonus+TeamBonus) AS Bonus,TeamSize, ROW_NUMBER() OVER (ORDER BY SUM( ISNULL(Distance,0.0000000) +ISNULL(MemberBonus,0.0000000)+ISNULL(TeamBonus,0.0000000)) DESC) AS Place FROM ( -- Sum the Distance, Member Bonus and Team Bonus and assign them a rank based on the sum value. SELECT TeamID, Name, Country, Region, Minutes, Distance, ISNULL(MemberBonus,0.0000000) AS MemberBonus, SUM(ISNULL(teamBonusData.BonusPoints,0.0000000)) AS TeamBonus, OfficeLocation, TeamSize FROM ( SELECT Team.TeamID, Team.Name, Result.Country AS Country, Result.Region, Result.Minutes AS Minutes, SUM(ISNULL(Result.MemberBonus,0)) AS MemberBonus, Result.Distance AS Distance, TeamSize FROM ( SELECT Group1.TeamID, Group1.Name, Country, Region, Minutes, Distance, MemberBonus, TeamSize FROM ( --Get a sum of distance covered by the team's members. Only get the data for the active teams ( Status = 1) SELECT Team.TeamID, Team.Name, Country.Name AS Country, Region.Name AS Region, ISNULL(SUM(Activity.Minutes),0) AS Minutes, ISNULL(SUM(Activity.Distance),0) AS Distance FROM Team LEFT JOIN Country ON Team.fk_CountryID = Country.CountryID LEFT JOIN Region ON Team.fk_RegionID = Region.RegionID JOIN TeamMember LEFT JOIN Member LEFT JOIN Activity ON Activity.fk_MemberID = Member.MemberID ON Member.MemberID = TeamMember.MemberID ON Team.TeamID = TeamMember.TeamID WHERE Team.Status = 1 AND Member.Disabled = 0 GROUP BY TeamMember.TeamID, Team.TeamID, Team.Name, Country.Name, Region.Name )Group1 JOIN ( -- Get a sum of Bonus points given to the team's members. SELECT TeamMember.TeamID, Member.MemberID, SUM(ISNULL(MemberBonus.BonusPoints,0)) AS MemberBonus FROM Team JOIN TeamMember JOIN Member LEFT JOIN dbo.MemberBonus ON Member.MemberID = MemberBonus.fk_MemberID ON TeamMember.MemberID = Member.MemberID ON Team.TeamID = TeamMember.TeamID GROUP BY Member.MemberID, TeamMember.TeamID ) Group2 ON Group1.TeamID = Group2.TeamID JOIN ( -- Get the team size ( number of members in the team) SELECT COUNT(TeamMember.TeamID) AS TeamSize,TeamID FROM TeamMember GROUP BY TeamMember.TeamID )Group3 ON Group1.TeamID = Group3.TeamID )Result JOIN Team ON Result.TeamID = Team.TeamID GROUP BY Team.TeamID, Team.Name, Result.Country, Result.Minutes, Result.Distance, Result.Region, Result.TeamSize )teamRank LEFT JOIN ( --Get the Bonus points given to the team SELECT ISNULL(TeamBonus.BonusPoints,0)AS BonusPoints, fk_TeamID FROM TeamBonus )teamBonusData ON teamRank.TeamID = teamBonusData.fk_TeamID LEFT JOIN ( -- Get the office location value for the team's Captain SELECT TeamID AS CapTeamID, OfficeLocation FROM TeamMember JOIN Member on TeamMember.MemberID = Member.MemberID WHERE MemberType='Captain' )captainData ON teamRank.TeamID = captainData.CapTeamID GROUP BY teamRank.TeamID, teamRank.Name, teamRank.Country, teamRank.Minutes, teamRank.Distance, teamRank.Region, teamRank.MemberBonus, captainData.OfficeLocation, teamRank.TeamSize ) myTeamRank GROUP BY myTeamRank.TeamID, myTeamRank.Name, myTeamRank.Country, myTeamRank.Minutes, myTeamRank.Distance, myTeamRank.Region, myTeamRank.OfficeLocation,TeamSize ORDER BY Place