Complex SQL query

Question

Gdyson 0 Newbie Poster

13 Years Ago

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

mssql

2 Contributors
2 Replies
103 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by Gdyson

huangzhi 0 Light Poster

13 Years Ago

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

Reply to this topic

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.

Gdyson 0 Newbie Poster · Answer 1 · 2010-12-14T16:10:31+00:00

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