Hi

I'm trying to query the following tables to find out the Resource Companyname and Project Title for any Recruiters who have worked on more than/less than 6 projects within a specified period.

These are the tables:

dbo.tblResources
ResourceID
Company Name

dbo.tblRecruiters
RecruiterID
ResourceID

dbo.tblProjects
ProjectID
Datecreated

dbo.tblProfiles
ProfileID
ProjectID
RecruiterID

Basically I need to locate on the Profiles table where a recruiterID occurs 6 or more times - but it often occurs more than once per ProjectID. And I have to be able to query within specified time periods eg - today and 6 months ago.

Below is the table code and insert data:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblResources](
[ResourceID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED
(
[ResourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblRecruiters](
[RecruiterID] [int] IDENTITY(1,1) NOT NULL,
[ResourceID] [int] NOT NULL,
CONSTRAINT [PK_tblRecruiters] PRIMARY KEY CLUSTERED
(
[RecruiterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblRecruiters] WITH CHECK ADD CONSTRAINT [FK_tblRecruiters_tblResources] FOREIGN KEY([ResourceID])
REFERENCES [dbo].[tblResources] ([ResourceID])
GO

ALTER TABLE [dbo].[tblRecruiters] CHECK CONSTRAINT [FK_tblRecruiters_tblResources]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblProjects](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](100) NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblProjects] ADD CONSTRAINT [DF_Project_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblProfiles](
[ProfileID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[RespondentID] [int] NULL,
[RecruiterID] [int] NOT NULL,
) ON [PRIMARY]
SET ANSI_PADDING OFF
SET ANSI_PADDING ON
ALTER TABLE [dbo].[tblProfiles] ADD CONSTRAINT [PK_tblProfiles] PRIMARY KEY CLUSTERED
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblProfiles] ADD CONSTRAINT [DF_tblProfiles_RecruiterID] DEFAULT ((0)) FOR [RecruiterID]
GO

INSERT INTO [dbo].[tblResources]
([CompanyName])
VALUES
('Research Opinions', varchar(50))
GO

INSERT INTO [dbo].[tblResources]
([CompanyName])
VALUES
('Research Recruitment', varchar(50))
GO

INSERT INTO [dbo].[tblResources]
([CompanyName])
VALUES
('Focus Recruitment', varchar(50))
GO

INSERT INTO [dbo].[tblResources]
([CompanyName])
VALUES
('Focus Research', varchar(50))
GO

INSERT INTO [dbo].[tblRecruiters]
([ResourceID])
VALUES
(1, int,)
GO

INSERT INTO [dbo].[tblRecruiters]
([ResourceID])
VALUES
(2, int,)
GO

INSERT INTO [dbo].[tblRecruiters]
([ResourceID])
VALUES
(3, int,)
GO

INSERT INTO [dbo].[tblRecruiters]
([ResourceID])
VALUES
(4, int,)
GO

INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project One', varchar(100),
,2010-02-09 09:47:14.540, datetime,)
GO

INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Two', varchar(100),
,2010-03-09 09:47:14.540, datetime,)

INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Three', varchar(100),
,2010-04-09 09:47:14.540, datetime,)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Four', varchar(100),
,2010-05-09 09:47:14.540, datetime,)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Five', varchar(100),
,2010-06-09 09:47:14.540, datetime,)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Six', varchar(100),
,2010-07-09 09:47:14.540, datetime,)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Seven', varchar(100),
,2010-08-09 09:47:14.540, datetime,)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Eight', varchar(100),
,2010-09-09 09:47:14.540, datetime,)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Nine', varchar(100),
,2010-10-09 09:47:14.540, datetime,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(1, int,
,1, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(1, int,
,2, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(1, int,
,3, int,
,2, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(1, int,
,4, int,
,2, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2, int,
,5, int,
,3, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2, int,
,6, int,
,3, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2, int,
,7, int,
,3, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2, int,
,8, int,
,4, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2, int,
,9, int,
,4, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(3, int,
,10, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(4, int,
,11, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(4, int,
,12, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(4, int,
,13, int,
,4, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(4, int,
,14, int,
,4, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5, int,
,15, int,
,2, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5, int,
,16, int,
,2, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5, int,
,17, int,
,2, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5, int,
,18, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5, int,
,19, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(6, int,
,20, int,
,2, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(6, int,
,21, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(6, int,
,22, int,
,3, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(6, int,
,23, int,
,4, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(7, int,
,24, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(7, int,
,25, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8, int,
,26, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8, int,
,27, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8, int,
,28, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8, int,
,29, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8, int,
,30, int,
,1, int,)
GO

INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8, int,
,31, int,
,1, int,)
GO

Use DISTINCT to eliminate double row in Pf.RecruiterID, Pf.ProjectID. Then count the ProjectID
The query may be looks like below:

declare @StartDate smalldatetime,
	@EndDate smalldatetime

select	@StartDate = '2010/01/01',
	@EndDate = '2010/12/31'

select	RecruiterID, count(*)
  from (select	distinct Pf.RecruiterID, Pf.ProjectID
	  from	tblProfiles Pf inner join tblProjects Pr on
		Pf.ProjectID = Pr.ProjectID
	  where Pr.DateCreated >= @Startdate and 
		Pr.DateCreated < @EndDate + 1
	) X
  group by RecruiterID
  having count(*) >= 6

That query isn't recognising that a recruiterID can occur multiple times against any projectID, but with a different respondentID.

This is the query I have so far but the date part isn't working for me - its returning results outside of the 6 month window specified. If I remove that clause it still returns the same results.

select distinct r.CompanyName, p.projectID, p.Title, p.datecreated
from tblResources r
join tblRecruiters rc on rc.ResourceID = r.ResourceID
join tblProfiles pr on pr.recruiterID = rc.recruiterID
join tblProjects p on p.ProjectID = pr.projectID
where rc.recruiterid in
(select pr.recruiterid
from tblProfiles pr
join tblProjects p on p.projectID = pr.projectid
where p.datecreated >= DATEADD (mm,-6,getdate())
group by pr.recruiterid
having count(distinct pr.projectid)>= 6
)
order by r.companyName, p.projectID
go

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.