Get team Stats in SQL Server

I am trying to get team Stats, most of the stats I got it however I need some suggestion's.

Create tables:
EATE TABLE [dbo].[Games](
      [GameID] [int] NOT NULL,
      [HomeTeamID] [int] NOT NULL,
      [AwayTeamID] [int] NOT NULL,
      [GameDateTime] [datetime] NOT NULL,
      [HomeScore] [int] NOT NULL,
      [AwayScore] [int] NOT NULL,
      [MVPPlayerID] [int] NOT NULL,
 CONSTRAINT [PK_Games] PRIMARY KEY CLUSTERED
(
      [GameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Players](
      [PlayerID] [int] NOT NULL,
      [Name] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Players] PRIMARY KEY CLUSTERED
(
      [PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Team_Player](
      [TeamID] [int] NOT NULL,
      [PlayerID] [int] NOT NULL,
 CONSTRAINT [PK_Team_Player] PRIMARY KEY CLUSTERED
(
      [PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Teams](
      [TeamID] [int] NOT NULL,
      [Name] [varchar](100) NOT NULL,
      [Stadium] [varchar](100) NOT NULL,
      [Logo] [varchar](100) NULL,
      [URL] [varchar](50) NULL,
 CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED
(
      [TeamID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

From above schema I need to find status for each team

Team_name, stadium, URL, games_played, last_game_stadium, last_game_date, most_valuable_player.

How should I found this?
Yaadgar KhanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
There is no test data. But does this give you the stuff you need
WITH Stas AS (

SELECT Teams.TeamID, Teams.Name Team_Name, Teams.Stadium, Teams.Url, COUNT(Games.GameID) AS games_played,  MAX(Games.GameDateTime) AS last_game_date 
FROM Teams
     INNER JOIN Games ON Games.HomeTeamID = Team.TeamID OR Games.AwayTeamID = Teams.TeamID
GROUP BY Teams.TeamID, Teams.Name, Teams.Stadium, Teams.Url
)

SELECT Stats.*, HomeTeam.Stadium last_game_stadium, Players.Name most_valuable_player
FROM Stats
    INNER JOIN Games ON (Games.HomeTeamID = Team.TeamID OR Games.AwayTeamID = Teams.TeamID) AND Games.GameDateTime = Stats.last_game_date 
    INNER JOIN Teams HomeTeam ON Games.HomeTeamID = HomeTeam.TeamID
    INNER JOIN Players ON Games.MVPPlayerID = Players.PlayerID

Open in new window

Yaadgar KhanAuthor Commented:
Database Schema:
CREATE TABLE [dbo].[Games](
	[GameID] [int] NOT NULL,
	[HomeTeamID] [int] NOT NULL,
	[AwayTeamID] [int] NOT NULL,
	[GameDateTime] [datetime] NOT NULL,
	[HomeScore] [int] NOT NULL,
	[AwayScore] [int] NOT NULL,
	[MVPPlayerID] [int] NOT NULL,
 CONSTRAINT [PK_Games] PRIMARY KEY CLUSTERED 
(
	[GameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Players](
	[PlayerID] [int] NOT NULL,
	[Name] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Players] PRIMARY KEY CLUSTERED 
(
	[PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Team_Player](
	[TeamID] [int] NOT NULL,
	[PlayerID] [int] NOT NULL,
 CONSTRAINT [PK_Team_Player] PRIMARY KEY CLUSTERED 
(
	[PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Teams](
	[TeamID] [int] NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[Stadium] [varchar](100) NOT NULL,
	[Logo] [varchar](100) NULL,
	[URL] [varchar](50) NULL,
 CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED 
(
	[TeamID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


SAMPLE DATA
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (1, N'Ty Lawson')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (2, N'Kevin Faried')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (3, N'Labron James')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (4, N'Chris Bosh')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (5, N'DeMar DeRozan')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (6, N'Kyle Lowry')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (7, N'Tim Duncan')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (8, N'Tony Parker')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (9, N'Paul George')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (10, N'Roy Hibbert')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (11, N'Kevin Durant')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (12, N'Russell Westbrook')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (13, N'James Harden')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (14, N'Chandler Parsons')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (15, N'Carlos Boozer')
INSERT [dbo].[Players] ([PlayerID], [Name]) VALUES (16, N'Taj Gibson')
INSERT [dbo].[Teams] ([TeamID], [Name], [Stadium], [Logo], [URL]) VALUES (1, N'Denver Nuggets', N'Pepsi Center', N'', N'')
INSERT [dbo].[Teams] ([TeamID], [Name], [Stadium], [Logo], [URL]) VALUES (2, N'Miami Heat', N'American Airlines Arena', N'', N'')
INSERT [dbo].[Teams] ([TeamID], [Name], [Stadium], [Logo], [URL]) VALUES (3, N'Toronto Raptors', N'Air Canada Centre', N'', N'')
INSERT [dbo].[Teams] ([TeamID], [Name], [Stadium], [Logo], [URL]) VALUES (4, N'San Antonio Spurs', N'AT&T Center', N'', N'')
INSERT [dbo].[Teams] ([TeamID], [Name], [Stadium], [Logo], [URL]) VALUES (5, N'Indiana Pacers', N'Bankers Life Fieldhouse', N'', N'')
INSERT [dbo].[Teams] ([TeamID], [Name], [Stadium], [Logo], [URL]) VALUES (6, N'Oklahoma City Thunder', N'Chesapeake Energy Arena', N'', N'')
INSERT [dbo].[Teams] ([TeamID], [Name], [Stadium], [Logo], [URL]) VALUES (7, N'Houston Rockets', N'Toyota Center', N'', N'')
INSERT [dbo].[Teams] ([TeamID], [Name], [Stadium], [Logo], [URL]) VALUES (8, N'Chicago Bulls', N'United Center', N'', N'')
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (1, 1, 2, CAST(0x0000A26900000000 AS DateTime), 111, 98, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (2, 2, 3, CAST(0x0000A26A00000000 AS DateTime), 90, 92, 3)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (3, 3, 4, CAST(0x0000A26B00000000 AS DateTime), 86, 96, 5)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (4, 4, 5, CAST(0x0000A26C00000000 AS DateTime), 78, 101, 8)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (5, 5, 6, CAST(0x0000A26D00000000 AS DateTime), 98, 112, 10)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (6, 6, 7, CAST(0x0000A26E00000000 AS DateTime), 92, 81, 12)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (7, 7, 8, CAST(0x0000A26F00000000 AS DateTime), 96, 85, 14)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (8, 8, 1, CAST(0x0000A27000000000 AS DateTime), 101, 84, 16)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (9, 1, 3, CAST(0x0000A27100000000 AS DateTime), 112, 83, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (10, 2, 4, CAST(0x0000A27200000000 AS DateTime), 81, 98, 3)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (11, 3, 5, CAST(0x0000A27300000000 AS DateTime), 85, 92, 5)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (12, 4, 6, CAST(0x0000A27400000000 AS DateTime), 84, 96, 7)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (13, 5, 7, CAST(0x0000A27500000000 AS DateTime), 83, 101, 9)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (14, 6, 8, CAST(0x0000A27600000000 AS DateTime), 81, 112, 15)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (15, 7, 1, CAST(0x0000A27700000000 AS DateTime), 80, 81, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (16, 8, 2, CAST(0x0000A27800000000 AS DateTime), 98, 85, 16)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (17, 1, 4, CAST(0x0000A27900000000 AS DateTime), 99, 84, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (18, 2, 5, CAST(0x0000A27A00000000 AS DateTime), 115, 83, 4)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (19, 3, 6, CAST(0x0000A27B00000000 AS DateTime), 114, 98, 6)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (20, 4, 7, CAST(0x0000A27C00000000 AS DateTime), 102, 92, 8)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (21, 5, 8, CAST(0x0000A27D00000000 AS DateTime), 111, 96, 10)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (22, 6, 1, CAST(0x0000A27E00000000 AS DateTime), 90, 101, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (23, 7, 2, CAST(0x0000A27F00000000 AS DateTime), 86, 112, 3)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (24, 8, 3, CAST(0x0000A28000000000 AS DateTime), 78, 81, 15)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (25, 1, 5, CAST(0x0000A28100000000 AS DateTime), 98, 85, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (26, 2, 6, CAST(0x0000A28200000000 AS DateTime), 92, 84, 12)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (27, 3, 7, CAST(0x0000A28300000000 AS DateTime), 96, 83, 5)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (28, 4, 8, CAST(0x0000A28400000000 AS DateTime), 101, 98, 8)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (29, 5, 1, CAST(0x0000A28500000000 AS DateTime), 112, 92, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (30, 6, 2, CAST(0x0000A28600000000 AS DateTime), 81, 96, 3)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (31, 7, 3, CAST(0x0000A28700000000 AS DateTime), 85, 101, 14)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (32, 8, 4, CAST(0x0000A28800000000 AS DateTime), 84, 112, 15)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (33, 1, 2, CAST(0x0000A28900000000 AS DateTime), 83, 81, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (34, 2, 3, CAST(0x0000A28A00000000 AS DateTime), 81, 85, 3)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (35, 3, 4, CAST(0x0000A28B00000000 AS DateTime), 80, 84, 6)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (36, 4, 5, CAST(0x0000A28C00000000 AS DateTime), 98, 83, 8)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (37, 5, 6, CAST(0x0000A28D00000000 AS DateTime), 99, 98, 9)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (38, 6, 7, CAST(0x0000A28E00000000 AS DateTime), 115, 92, 12)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (39, 7, 8, CAST(0x0000A28F00000000 AS DateTime), 114, 96, 14)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (40, 8, 1, CAST(0x0000A29000000000 AS DateTime), 102, 101, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (41, 1, 3, CAST(0x0000A29100000000 AS DateTime), 111, 112, 2)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (42, 2, 4, CAST(0x0000A29200000000 AS DateTime), 90, 81, 4)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (43, 3, 5, CAST(0x0000A29300000000 AS DateTime), 86, 85, 6)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (44, 4, 6, CAST(0x0000A29400000000 AS DateTime), 78, 84, 11)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (45, 5, 7, CAST(0x0000A29500000000 AS DateTime), 98, 83, 10)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (46, 6, 8, CAST(0x0000A29600000000 AS DateTime), 92, 98, 16)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (47, 7, 1, CAST(0x0000A29700000000 AS DateTime), 96, 92, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (48, 8, 2, CAST(0x0000A29800000000 AS DateTime), 101, 96, 16)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (49, 1, 4, CAST(0x0000A29900000000 AS DateTime), 112, 101, 2)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (50, 2, 5, CAST(0x0000A29A00000000 AS DateTime), 81, 112, 9)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (51, 3, 6, CAST(0x0000A29B00000000 AS DateTime), 85, 81, 5)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (52, 4, 7, CAST(0x0000A29C00000000 AS DateTime), 84, 85, 7)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (53, 5, 8, CAST(0x0000A29D00000000 AS DateTime), 83, 84, 16)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (54, 6, 1, CAST(0x0000A29E00000000 AS DateTime), 81, 83, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (55, 7, 2, CAST(0x0000A29F00000000 AS DateTime), 80, 98, 3)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (56, 8, 3, CAST(0x0000A2A000000000 AS DateTime), 98, 92, 15)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (57, 1, 5, CAST(0x0000A2A100000000 AS DateTime), 99, 96, 9)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (58, 2, 6, CAST(0x0000A2A200000000 AS DateTime), 115, 101, 3)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (59, 3, 7, CAST(0x0000A2A300000000 AS DateTime), 114, 112, 13)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (60, 4, 8, CAST(0x0000A2A400000000 AS DateTime), 102, 81, 15)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (61, 5, 1, CAST(0x0000A2A500000000 AS DateTime), 78, 85, 1)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (62, 6, 2, CAST(0x0000A2A600000000 AS DateTime), 76, 84, 3)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (63, 7, 3, CAST(0x0000A2A700000000 AS DateTime), 79, 83, 13)
INSERT [dbo].[Games] ([GameID], [HomeTeamID], [AwayTeamID], [GameDateTime], [HomeScore], [AwayScore], [MVPPlayerID]) VALUES (64, 8, 4, CAST(0x0000A2A800000000 AS DateTime), 68, 116, 7)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (1, 1)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (1, 2)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (2, 3)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (2, 4)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (3, 5)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (3, 6)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (4, 7)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (4, 8)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (5, 9)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (5, 10)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (6, 11)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (6, 12)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (7, 13)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (7, 14)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (8, 15)
INSERT [dbo].[Team_Player] ([TeamID], [PlayerID]) VALUES (8, 16)

Open in new window



I need to find Following View

Team_Name, Team_Stadium, Team_url, games_palyed, home_played, away_played, total_win, total_lost, biggest_win, Biggest_lost, last_game_stadium, last_game_date, most_valuable_team_player





Solution I have Applied:
;WITH CTE  AS 
	(SELECT	
			T.TeamID,
			T.Name,	
			STADIUM			= T.Stadium,
			LOGO			= T.URL,
			PLAYED			= COUNT(C.HOME),
			WON				= SUM(CASE WHEN C.HomeScore > C.AwayScore THEN 1 ELSE 0 END),
			LOST			= SUM(CASE WHEN C.HomeScore < C.AwayScore THEN 1 ELSE 0 END),
			PLAYED_HOME		= COUNT(CASE WHEN C.HOME = 1 THEN C.HOME END),
			PLAYED_AWAY		= COUNT(CASE WHEN C.HOME = 0 THEN C.HOME END),
			BIGGEST_WIN		= MAX(CASE WHEN C.HomeScore > C.AwayScore THEN  CAST(C.HomeScore AS VARCHAR(4)) + '-'  + CAST(C.AwayScore AS VARCHAR(4)) END),
			BIGGEST_LOSS	= MAX(CASE WHEN C.HomeScore < C.AwayScore THEN  CAST(C.HomeScore AS VARCHAR(4)) + '-'  + CAST(C.AwayScore AS VARCHAR(4)) END),
			LAST_GAME_DATE	= MAX(C.GameDateTime),
			LAST_GAME_STADIUM = '',
			MVP_PLAYERS		= ''
	FROM [Teams] T
	CROSS APPLY (
		SELECT	T.TeamID,
				T.Name,
				G.HomeTeamID,
				G.HomeScore,
				G.AwayTeamID,
				G.AwayScore,
				G.GameDateTime,
				1 AS HOME
		FROM	Games G
		WHERE	G.HomeTeamID = T.TeamID
		UNION ALL
		SELECT	T.TeamID,
				T.Name,
				G.HomeTeamID,
				G.HomeScore,
				G.AwayTeamID,
				G.AwayScore,
				G.GameDateTime,
				0 AS HOME
		FROM	Games G
		WHERE	G.AwayTeamID = T.TeamID

	) C
	GROUP BY T.TeamID, T.Name, T.Stadium, T.URL)
SELECT * FROM CTE

Open in new window

MlandaTCommented:
WITH Stats AS (
	SELECT 
		Teams.TeamID, Teams.Name Team_Name, Teams.Stadium, Teams.Url, 
		SUM(CASE WHEN Games.HomeTeamID = Teams.TeamID THEN 1 ELSE 0 END) home_played,
		SUM(CASE WHEN Games.AwayTeamID = Teams.TeamID THEN 1 ELSE 0 END) away_played,
		COUNT(Games.GameID) AS games_played,  
		SUM(CASE 
				WHEN Games.HomeTeamID = Teams.TeamID AND Games.HomeScore > Games.AwayScore THEN 1 --we are home team, we win
				WHEN Games.AwayTeamID = Teams.TeamID AND Games.AwayScore > Games.HomeScore THEN 1 --we are away team, we win
				ELSE 0 
			END) total_win,
		SUM(CASE 
				WHEN Games.HomeTeamID = Teams.TeamID AND Games.HomeScore < Games.AwayScore THEN 1 --we are home team, we lose
				WHEN Games.AwayTeamID = Teams.TeamID AND Games.AwayScore < Games.HomeScore THEN 1 --we are away team, we lose
				ELSE 0 
			END) total_loss,
		SUM(CASE WHEN Games.HomeScore = Games.AwayScore THEN 1 ELSE 0 END) total_draw,
		MAX(CASE 
				WHEN Games.HomeTeamID = Teams.TeamID AND Games.HomeScore > Games.AwayScore THEN Games.HomeScore - Games.AwayScore --we are home team, we won, get margin
				WHEN Games.AwayTeamID = Teams.TeamID AND Games.AwayScore > Games.HomeScore THEN Games.AwayScore - Games.HomeScore --we are away team, we won, get margin
				ELSE 0 
			END) biggest_win,
		SUM(CASE 
				WHEN Games.HomeTeamID = Teams.TeamID AND Games.HomeScore < Games.AwayScore THEN Games.AwayScore - Games.HomeScore --we are home team, we lost, get margin
				WHEN Games.AwayTeamID = Teams.TeamID AND Games.AwayScore < Games.HomeScore THEN Games.HomeScore - Games.AwayScore --we are away team, we lost, get margin
				ELSE 0 
			END) biggest_loss,
		MAX(Games.GameDateTime) AS last_game_date 
	FROM Teams
		 INNER JOIN Games ON Games.HomeTeamID = Teams.TeamID OR Games.AwayTeamID = Teams.TeamID
	GROUP BY Teams.TeamID, Teams.Name, Teams.Stadium, Teams.Url
)

SELECT Stats.*, HomeTeam.Stadium last_game_stadium, Players.Name most_valuable_player
FROM Stats
    INNER JOIN Games ON (Games.HomeTeamID = Stats.TeamID OR Games.AwayTeamID = Stats.TeamID) AND Games.GameDateTime = Stats.last_game_date 
    INNER JOIN Teams HomeTeam ON Games.HomeTeamID = HomeTeam.TeamID
    INNER JOIN Players ON Games.MVPPlayerID = Players.PlayerID

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Yaadgar KhanAuthor Commented:
Thanks @MlandaT however last_game_date, last_game_stadium and most_valuable_player are not right

I have created sqlfiddle
http://sqlfiddle.com/#!3/fd9e13/1
MlandaTCommented:
In which case are they not right? Take the Denver Nuggets (TeamID = 1), the query says their last game was 2013-12-31 00:00:00.000 at "Bankers Life Fieldhouse". This agrees with what this query says:
select * from games 
where hometeamid=1 or awayteamid=1 --in their last game, they were either home or away
order by gamedatetime desc  --this gets the most recent (same as Max on the GameDateTime in the other query

Open in new window

Try each TeamID against the data you sent
Yaadgar KhanAuthor Commented:
Thanks @mlandaT, if you see second row "Ty Lawson" is most valuable player for "Indiana Pacers" however Ty Lawson don't play for Indiana Pacers.

Select * From Team T
Join Team_player TP on T.TeamID = TP.TeamID
Join Players P ON P.PlayerID = TP.PlayerID and TP.TeamID=T.TeamID

Open in new window

MlandaTCommented:
PlayerIf I look at the last gane for TeamID-5, the MVPPlayerID=1. In the Players table PlayerID 1 is Ty Lawson.
MlandaTCommented:
You probably need to explain how your Team_Players table works.

EXPLAIN
Select * From Team T
Join Team_player TP on T.TeamID = TP.TeamID
Join Players P ON P.PlayerID = TP.PlayerID and TP.TeamID=T.TeamID

A game has 2 teams (Home and Away). You have one MVPPlayerID column. You are suggesting that a TeamID needs to be supplied to the JOIN with Team_Player table. Is this the HomeTeam or the AwayTeam. The MVP Player can come from either one of the 2 teams playing the game.
Yaadgar KhanAuthor Commented:
MVP will come for all games performance for the team. If we count the performance for the players across all the games we get MVP for teams.

Select G.homeTeamId, T.Name,P.Name,P.PlayerID,
COUNT(G.MVPPlayerID) as MVP_Count
From Games G
Left Outer Join Players P ON P.PlayerID =G.MVPPlayerID
Left Outer Join Team T ON T.TeamID =G.HomeTeamID
Left Outer Join Team_Player TP ON TP.TeamID=T.TeamID
Where TP.PlayerID =G.MVPPlayerID
Group By G.HomeTeamID, T.Name, P.Name, P.PlayerID
Order By G.HomeTeamID, COUNT(G.MVPPlayerID) DESC

Open in new window

Yaadgar KhanAuthor Commented:
Above script show

Ty Lawson is MVP for Denver Nuggets because he was 5 times MVP in games and he play for Denver Nuggets.

Labron James is MVP for Miami Heat as he was 4 times MVP in games.

Roy Hibbet is MVP for Indiana Pacers as he was 3 times MVP in games and he play for Indiana Pacers
Yaadgar KhanAuthor Commented:
I've requested that this question be deleted for the following reason:

Data is wrong
MlandaTCommented:
A solution was provided to address most of the requirements except the MVP player. There was a question around the derivation of the MVP Player. Author only posted table structures without any documentation explaining the structure or relationships and i took time to look at this and help. I helped to give the Author quite enough of a direction in which he/she could build on. We are not far from a complete solution here. It might be unrealistic to expect a 100% correct answer with incomplete specifications. IMHO, the community exists to give direction and guidance and actual solutions where possible, points should indeed be awarded for effort and for giving direction.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.