at this point, everything is working as is, except if a team has not played any games, their records of 0-0-0 will not show up. I've tried changing the inner joins to tTeams to left outer joins, but that didn't work either.

here's my current query:

select T.Team, T.Color, isnull(P.firstname,'Captain') + ' ' + isnull(P.lastname,'Unknown') as Captain , sum(wins) as Wins , sum(ties) as Ties , sum(losses) as Losses , sum( wins + Ties + Losses ) as GamesPlayed , sum(GoalsFor) as GoalsFor , sum(GoalsAgainst) as GoalsAgainst , sum(case when OT = 'N' and wins = 1 then 1 else 0 end) as RegulationWins , sum(case when OT = 'Y' and wins = 1 then 1 else 0 end) as OTWins , sum(case when OT = 'Y' and ties = 1 then 1 else 0 end) as OTTies , sum(case when OT = 'Y' and losses = 1 then 1 else 0 end) as OTLosses , sum(case when divisional = 'Y' and wins = 1 then 1 else 0 end) as DivisionalWins , sum(case when divisional = 'Y' and Ties = 1 then 1 else 0 end) as DivisionalTies , sum(case when divisional = 'Y' and losses = 1 then 1 else 0 end) as DivisionalLosses , sum(case when OT = 'N' and wins = 1 then 3 when OT = 'Y' and wins = 1 then 2 when OT = 'Y' and losses = 1 then 1 else 0 end) as points from ( select G.Gameid, 'H' as home_away, Hometeam as teamid, homescore as GoalsFor, awayscore as GoalsAgainst , case when homescore > awayscore then 1 else 0 end as wins , case when homescore = awayscore then 1 else 0 end as ties , case when homescore < awayscore then 1 else 0 end as losses , case when h.divisionid=a.divisionid and h.divisionid = @divisionID then 'Y' else 'N' end as divisional , h.divisionid , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT from tGames G inner join tteams as H on g.hometeam = h.teamid inner join tteams as A on g.awayteam = a.teamid where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID ) union all select G.Gameid, 'A' as home_away, Awayteam as teamid, awayscore as GoalsFor, homescore as GoalsAgainst , case when homescore < awayscore then 1 else 0 end as wins , case when homescore = awayscore then 1 else 0 end as ties , case when homescore > awayscore then 1 else 0 end as losses , case when h.divisionid=a.divisionid and a.divisionid = @divisionID then 'Y' else 'N' end as divisional , a.divisionid , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT from tGames G inner join tteams as H on g.hometeam = h.teamid inner join tteams as A on g.awayteam = a.teamid where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID ) ) games inner join tTeams T on games.teamid = T.teamid left outer join tPlayers P on T.captainid = P.playerid where t.divisionID = @divisionID Group By T.Team, T.Color, isnull(P.firstname,'Captain'), isnull(P.lastname,'Unknown')Order By points DESC

from tteams as H left join tGames G on g.hometeam = h.teamid left join tteams as A on g.awayteam = a.teamidand for the away side of the union all from tteams as A left join tGames G on g.hometeam = a.teamid left join tteams as H on g.awayteam = h.teamid

select T.Team, T.Color, isnull(P.firstname,'Captain') + ' ' + isnull(P.lastname,'Unknown') as Captain , sum(wins) as Wins , sum(ties) as Ties , sum(losses) as Losses , sum( wins + Ties + Losses ) as GamesPlayed , sum(GoalsFor) as GoalsFor , sum(GoalsAgainst) as GoalsAgainst , sum(case when OT = 'N' and wins = 1 then 1 else 0 end) as RegulationWins , sum(case when OT = 'Y' and wins = 1 then 1 else 0 end) as OTWins , sum(case when OT = 'Y' and ties = 1 then 1 else 0 end) as OTTies , sum(case when OT = 'Y' and losses = 1 then 1 else 0 end) as OTLosses , sum(case when divisional = 'Y' and wins = 1 then 1 else 0 end) as DivisionalWins , sum(case when divisional = 'Y' and Ties = 1 then 1 else 0 end) as DivisionalTies , sum(case when divisional = 'Y' and losses = 1 then 1 else 0 end) as DivisionalLosses , sum(case when OT = 'N' and wins = 1 then 3 when OT = 'Y' and wins = 1 then 2 when OT = 'Y' and losses = 1 then 1 else 0 end) as points from tTeams T left outer join ( select G.Gameid, 'H' as home_away, Hometeam as teamid, homescore as GoalsFor, awayscore as GoalsAgainst , case when homescore > awayscore then 1 else 0 end as wins , case when homescore = awayscore then 1 else 0 end as ties , case when homescore < awayscore then 1 else 0 end as losses , case when h.divisionid=a.divisionid and h.divisionid = @divisionID then 'Y' else 'N' end as divisional , h.divisionid , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT from tGames G inner join tteams as H on g.hometeam = h.teamid inner join tteams as A on g.awayteam = a.teamid where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID ) union all select G.Gameid, 'A' as home_away, Awayteam as teamid, awayscore as GoalsFor, homescore as GoalsAgainst , case when homescore < awayscore then 1 else 0 end as wins , case when homescore = awayscore then 1 else 0 end as ties , case when homescore > awayscore then 1 else 0 end as losses , case when h.divisionid=a.divisionid and a.divisionid = @divisionID then 'Y' else 'N' end as divisional , a.divisionid , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT from tGames G inner join tteams as H on g.hometeam = h.teamid inner join tteams as A on g.awayteam = a.teamid where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID ) ) games on games.teamid = T.teamid left outer join tPlayers P on T.captainid = P.playerid where t.divisionID = @divisionID Group By T.Team, T.Color, isnull(P.firstname,'Captain'), isnull(P.lastname,'Unknown')Order By points DESC

*laughing* looks like I am too late to the party :)

The change was adding in a new join to that union subquery (with the alias name of "games"), so, select from teams and then left outer join to the "games" subquery rather than the games subquery doing an inner join to tteams.

Your other choice was to make that inner join to tteams a "right outer join" to tteams (meaning include everything in tteams even if it doesnt match the join criteria). And notice the use of ISNULL in the select to make sure you get the zero's that you asked for ...

e.g.

declare @gametype varchar(20) = 'regular'declare @season int = 2declare @divisionid int = 3select T.Team, T.Color, isnull(P.firstname,'Captain') + ' ' + isnull(P.lastname,'Unknown') as Captain , sum(isnull(wins,0)) as Wins , sum(isnull(ties,0)) as Ties , sum(isnull(losses,0)) as Losses , sum(isnull( (wins + Ties + Losses),0) ) as GamesPlayed , sum(isnull(GoalsFor,0)) as GoalsFor , sum(isnull(GoalsAgainst,0)) as GoalsAgainst , sum(case when OT = 'N' and wins = 1 then 1 else 0 end) as RegulationWins , sum(case when OT = 'Y' and wins = 1 then 1 else 0 end) as OTWins , sum(case when OT = 'Y' and ties = 1 then 1 else 0 end) as OTTies , sum(case when OT = 'Y' and losses = 1 then 1 else 0 end) as OTLosses , sum(case when divisional = 'Y' and wins = 1 then 1 else 0 end) as DivisionalWins , sum(case when divisional = 'Y' and Ties = 1 then 1 else 0 end) as DivisionalTies , sum(case when divisional = 'Y' and losses = 1 then 1 else 0 end) as DivisionalLosses , sum(case when OT = 'N' and wins = 1 then 3 when OT = 'Y' and wins = 1 then 2 when OT = 'Y' and losses = 1 then 1 else 0 end) as points from ( select G.Gameid, 'H' as home_away, Hometeam as teamid, homescore as GoalsFor, awayscore as GoalsAgainst , case when homescore > awayscore then 1 else 0 end as wins , case when homescore = awayscore then 1 else 0 end as ties , case when homescore < awayscore then 1 else 0 end as losses , case when h.divisionid=a.divisionid and h.divisionid = @divisionID then 'Y' else 'N' end as divisional , h.divisionid , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT from tGames G inner join tteams as H on g.hometeam = h.teamid inner join tteams as A on g.awayteam = a.teamid where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID ) union all select G.Gameid, 'A' as home_away, Awayteam as teamid, awayscore as GoalsFor, homescore as GoalsAgainst , case when homescore < awayscore then 1 else 0 end as wins , case when homescore = awayscore then 1 else 0 end as ties , case when homescore > awayscore then 1 else 0 end as losses , case when h.divisionid=a.divisionid and a.divisionid = @divisionID then 'Y' else 'N' end as divisional , a.divisionid , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT from tGames G inner join tteams as H on g.hometeam = h.teamid inner join tteams as A on g.awayteam = a.teamid where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID ) ) games right outer join tTeams T on games.teamid = T.teamid left outer join tPlayers P on T.captainid = P.playerid where t.divisionID = @divisionID Group By T.Team, T.Color, isnull(P.firstname,'Captain'), isnull(P.lastname,'Unknown')Order By points DESC

I moved the "inner join tTeams T on games.teamid = T.teamid" on line 47 of your original query to above the inline view so that the "tTeams T" so that the table would be on the left when 'left join'ed.
As @mark_wills pointed out ... it could have remained where it was and a 'right join' done. I preferred to move it as you already had a left join from it, purely as a style preference of mine, so that it would be two left join's.

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days.
Lets' understand the Au…