December 10, 2010 at 9:09 am
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
I'm sure this is a really simple thing to do but I'm not sure how to do Select IF statements with Inner Joins.
Any help would be appreciated.
December 10, 2010 at 2:17 pm
There are plenty of people on here willing and able to help you. However, you need to help us help you. Table create statements, test data and desired output. Would be helpful if you also include what you have tried so far.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2010 at 7:45 am
Hope this helps:
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
December 13, 2010 at 7:51 am
Based on your sample data, what is your expected result?
And what have you tried so far?
December 13, 2010 at 8:11 am
If I was to run a query for the past 7 months - from today's date, I should find that ResourceID 1 - 'Research Opinions', has worked on 6 or more projects within that timeframe.
I have no idea how to put this altogether as a query.
Essentially I am trying to find on the Profiles table where a recruiterID occurs more or less than 6 - 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.
December 13, 2010 at 9:03 am
None of your insert statements will work. They all have the datatypes in the middle of them.
INSERT INTO [dbo].[tblResources]
([CompanyName])
VALUES
('Research Opinions', varchar(50))
Also, I am totally unclear on what you are trying to get out of this. You want all Profile records where count <> 6 grouped by ProjectID?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2010 at 9:08 am
Basically we need to perform audits on our recruiters every 6 months but it only applies to those who have worked on 6 or more projects in that time period. So we need to be able to query the system to find out which ones have worked on 6 or more projects within the specified time frame.
December 13, 2010 at 9:24 am
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')
GO
INSERT INTO [dbo].[tblResources]
([CompanyName])
VALUES
('Research Recruitment')
GO
INSERT INTO [dbo].[tblResources]
([CompanyName])
VALUES
('Focus Recruitment')
GO
INSERT INTO [dbo].[tblResources]
([CompanyName])
VALUES
('Focus Research')
GO
INSERT INTO [dbo].[tblRecruiters]
([ResourceID])
VALUES
(1)
GO
INSERT INTO [dbo].[tblRecruiters]
([ResourceID])
VALUES
(2)
GO
INSERT INTO [dbo].[tblRecruiters]
([ResourceID])
VALUES
(3)
GO
INSERT INTO [dbo].[tblRecruiters]
([ResourceID])
VALUES
(4)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project One',
2010-02-09 09:47:14.540)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Two'
,2010-03-09 09:47:14.540)
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Three'
,2010-04-09 09:47:14.540)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Four'
,2010-05-09 09:47:14.540)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Five',
2010-06-09 09:47:14.540)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Six'
,2010-07-09 09:47:14.540)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Seven'
,2010-08-09 09:47:14.540)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Eight'
,2010-09-09 09:47:14.540)
GO
INSERT INTO [dbo].[tblProjects]
([Title]
,[DateCreated])
VALUES
('Project Nine'
,2010-10-09 09:47:14.540)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(1
,1
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(1
,2
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(1
,3
,2)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(1
,4
,2)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2
,5
,3)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2
,6
,3)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2
,7
,3)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2
,8
,4)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(2
,9
,4)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(3
,10
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(4
,11
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(4
,12
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(4
,13
,4)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(4
,14
,4)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5
,15
,2)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5
,16
,2)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5
,17
,2)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5
,18
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(5
,19
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(6
,20
,2)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(6
,21
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(6
,22
,3)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(6
,23
,4)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(7
,24
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(7
,25
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8
,26
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8
,27
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8
,28
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8
,29
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8
,30
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8
,31
,1)
GO
INSERT INTO [dbo].[tblProfiles]
([ProjectID]
,[RespondentID]
,[RecruiterID])
VALUES
(8
,32
,1)
GO
December 13, 2010 at 9:31 am
garethdyson (12/13/2010)
Basically we need to perform audits on our recruiters every 6 months but it only applies to those who have worked on 6 or more projects in that time period. So we need to be able to query the system to find out which ones have worked on 6 or more projects within the specified time frame.
You don't have end dates fro projects, so you don't know which ones started more than 6 months ago have been worked on in the last 6 months. So I don't think the data you've shown us can be used to provide what you want. I guess if you only count projects that started in the last 6 months you can get the list of recruiters with a query like
select RecruiterID , count(*) as ProjectCount from dbo.tblProjects Proj, dbo.tblProfiles Prof
where Proj.Datecreated >= dateadd(mm,-6,getdate())
and Proj.ProjectID = Prof.ProjectID
group by RecruiterID
having count(*) >= 6
But of course I imagine you will want to plug a real date in rather than saying "6 months before I run the query".
Tom
December 13, 2010 at 10:04 am
The end date isn't important. The fact they're assigned to a project implies completion of the project.
The problem with that query is that it is counting every occurance of a recruiterid, but where the projectid is the same, it should only count as 1.
For example, manually counting through ALL the insert statements you get the following:
research opinions - 7 projects
research recruitment - 3 projects
focus research - 2 projects
focus research - 3 projects
Of course that doesn't take into account any specific time frame.
December 13, 2010 at 10:12 am
This get you there?
select RecruiterID , count(distinct Proj.ProjectID) as ProjectCount
from dbo.tblProjects Proj
join dbo.tblProfiles Prof on Proj.ProjectID = Prof.ProjectID
where Proj.Datecreated >= dateadd(mm,-6,getdate())
group by RecruiterID
having count(distinct Proj.ProjectID) >= 6
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2010 at 10:19 am
Shove Tom's query into an IN clause - is this what you are after?
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
(
-- this gets the number of projects
select pr.recruiterid
from tblProfiles pr
join tblProjects p on p.projectID = pr.projectid
where p.datecreated >= dateadd(mm,-6,getdate())
group by recruiterid
having count(pr.projectid) >= 6
)
order by r.companyName, p.projectID
December 13, 2010 at 10:46 am
Thewbert (12/13/2010)
Shove Tom's query into an IN clause - is this what you are after?
That still needs "distinct" adding as Sean suggested.
Tom
December 14, 2010 at 3:02 am
I've been using this and it seems to partially work, it's returning people who have worked on 6 or more projects, but it's completely ignoring the date part of the query. If I take it out altogether I get exactly the same results as I do with it.
I've also tried hard coding a specific date into it to find all those before it but it still returns the same result set.
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
December 15, 2010 at 12:22 am
garethdyson (12/14/2010)
I've been using this and it seems to partially work, it's returning people who have worked on 6 or more projects, but it's completely ignoring the date part of the query. If I take it out altogether I get exactly the same results as I do with it.I've also tried hard coding a specific date into it to find all those before it but it still returns the same result set.
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
The problem is that you use the date restrinction only in the inner query which generates the recruiter list, and then select all projects that those recruiters have ever worked on regardless of date in the outer query. The date restriction has to be included in the outer query as well as in the inner one.
I don't like using the same alias in both the outer query and the inner one, so I would change that too (although it looks as if it makes no difference here because the scopes are crystal clear), ending up with something like
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 p.datecreated >= DATEADD (mm,-6,getdate())
and rc.recruiterid in (
select prI.recruiterid
from tblProfiles prI
join tblProjects pI on pI.projectID = prI.projectid
where pI.datecreated >= DATEADD (mm,-6,getdate())
group by prI.recruiterid
having count(distinct prI.projectid)>= 6
)
order by r.companyName, p.projectID
Tom
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply