Projects Power Search Queries

A. List of Projects Power Search Names

B. Projects Power Search Queries

Project Code

SELECT ProjectsID FROM Projects WHERE JobCode

Project Position Title

SELECT ProjectsID FROM Projects WITH(NOLOCK) WHERE JobTitle

Project Posting has no Location

SELECT Projects.ProjectsID from Projects WITH (NOLOCK)

INNER JOIN WebJobPostings WITH (NOLOCK)  ON WebJobPostings.ProjectsID = Projects.ProjectsID

AND  LEN(RTRIM(WebJobPostings.Location)) = 0

Project Posting has no RoleCode

SELECT Projects.ProjectsID from Projects WITH (NOLOCK)

INNER JOIN WebJobPostings WITH (NOLOCK)  ON WebJobPostings.ProjectsID = Projects.ProjectsID

AND  (WebJobPostings.RoleCode1 IS NULL OR WebJobPostings.RoleCode1 = 0)

Project Posting Status is Draft

SELECT Projects.ProjectsID from Projects WITH (NOLOCK)

INNER JOIN WebJobPostings WITH (NOLOCK)  ON WebJobPostings.ProjectsID = Projects.ProjectsID

AND  WebJobPostings.Status LIKE 'Draft%'

Project Posting Status is Live

SELECT Projects.ProjectsID from Projects WITH (NOLOCK)

INNER JOIN WebJobPostings WITH (NOLOCK)  ON WebJobPostings.ProjectsID = Projects.ProjectsID

AND  WebJobPostings.Status LIKE 'Live%'

Project Status

SELECT ProjectsID FROM Projects WITH(NOLOCK) WHERE ProjectStatus

Project with Team Member Firstname

SELECT ProjectsClientTeams.ProjectsID

FROM ProjectsClientTeams WITH (NOLOCK)

INNER JOIN People WITH (NOLOCK) ON ProjectsClientTeams.PeopleID = People.PeopleID

WHERE People.Firstname

Project with Team Member Firstname

SELECT ProjectsClientTeams.ProjectsID

FROM ProjectsClientTeams WITH (NOLOCK)

INNER JOIN People WITH (NOLOCK) ON ProjectsClientTeams.PeopleID = People.PeopleID

WHERE People.Firstname

Project with Team Member Lastname

SELECT ProjectsClientTeams.ProjectsID

FROM ProjectsClientTeams WITH (NOLOCK)

INNER JOIN People WITH (NOLOCK) ON ProjectsClientTeams.PeopleID = People.PeopleID

WHERE People.Lastname

Projects CreatedOn

SELECT ProjectsID FROM Projects WITH(NOLOCK) WHERE CreatedOn

Projects on List With Name

SELECT RecordID FROM ListsDetails WITH(NOLOCK)

JOIN Lists ON( ListsDetails.ListID = Lists.ListsID)

WHERE Lists.Name

Projects with Postings

SELECT ProjectsID FROM WebJobPostings

WHERE (WebJobPostings.ProjectsID IS NOT NULL OR WebJobPostings.ProjectsID > 0)

AND (WebJobPostings.Status LIKE 'Live%' OR WebJobPostings.Status Like 'Draft%')

Projects Without Contact Register

SELECT ProjectsID FROM Projects WITH(NOLOCK)  WHERE ProjectsID NOT IN

( SELECT ProjectsID  FROM ProjectsTargetLists WITH(NOLOCK) )