Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
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.teamid
and 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
declare @gametype varchar(20) = 'regular'
declare @season int = 2
declare @divisionid int = 3
select 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
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.