requesting reports on players - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

requesting reports on players

Author

Topic

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-21 : 05:35:28

Hi there, I have a sports database and I want to run a SQL statement to find players that need scouting reports. I have a table called ScoutingReport where I store ID, ReportDate and PlayerID. I also have a table called Requests with the columns ID, PlayerID and DateRequested.

Can someone help me to build some SQL to find all the players who have an entry in the Requests table, but the entry must have a DateRequested which is greater than their last entry in the ReportDate in the ScoutingReport table

A player may or may not have an entry in the ScoutingReport table

MartinCroft
Starting Member

4 Posts

Posted - 2011-12-21 : 05:48:10

Hi If you post the table create tables. it will be easier to script a query that will work for you.

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-21 : 05:56:16

quote:
Originally posted by MartinCroft

Hi If you post the table create tables. it will be easier to script a query that will work for you.

GO
/****** Object: Table [dbo].[ScoutingReport] Script Date: 12/21/2011 10:55:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ScoutingReport](
[ID] [int] IDENTITY(0,1) NOT FOR REPLICATION NOT NULL,
[ReportDate] [datetime] NOT NULL,
[PlayerID] [int] NOT NULL
CONSTRAINT [pk_ScoutingReport] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Requests] Script Date: 12/21/2011 10:53:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Requests](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PlayerID] [int] NULL,
[DateRequested] [datetime] NULL,
[RequestedBy] [int] NULL,
CONSTRAINT [PK_Requests] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-21 : 11:00:40

Anyone got any ideas... its very urgent and I am totally stuck!!

mokru
Starting Member

4 Posts

Posted - 2011-12-21 : 16:43:29

Briefly looks to me like homework...

Problem is you allow NULL as the DateRequested. This means that some requests might get dropped off by the following interpretation:

[It's basically a join on PlayerID and Date]

Select P.*
FROM ScoutingReport AS P
INNER JOIN Requests R on
(
P.PlayerID = R.PlayerID
and
R.DateRequested > P.ReportDate
)

But this can be ammended as follows to include the requests that do not have dates:
Select P.*
FROM ScoutingReport AS P
INNER JOIN Requests R on
(
P.PlayerID = R.PlayerID
and
(
R.DateRequested > P.ReportDate
OR R.DateRequested IS NULL
)
)

Any others thinking this is the right interpretation?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources