Adding Search Queries to your MyPCR Screen - PCRecruiter Learning CenterPCRecruiter Learning Center

Company SQL Search Statements

To find companies entered in the last 7 days:

Companies.Company_id IN (SELECT Company_Id FROM company_activity WHERE Date_Entered [GE] #[[-7days]]# AND act_type = 'ADDCOM')

To find companies with an Activity written in the last 7 days:

Companies.Company_id IN (SELECT Company_Id FROM company_activity WHERE Last_Activity [GE] #[[-7days]]#)

To find companies with no names:

Companies.Company_id NOT IN (SELECT company_id FROM candidates)

To find companies specified as ‘Client Company’:

Companies.DefaultCo = 'X'

To find companies specified as ‘Client Company’ based on user name:

Companies.DefaultCo = 'X' AND Companies.user_name LIKE 'USERNAMEHERE'

To find companies that are not on a rollup:

Companies.Company_Id NOT IN (SELECT Company_Id FROM RollupC_Records)

To find companies with no positions:

Companies.company_id NOT IN (SELECT company_id FROM positions)

To find companies based on username of positions:

Companies.company_id IN (SELECT company_ID FROM positions WHERE user_name = 'USERNAMEHERE')

To find companies entered since ‘date’ that do not have keywords:

Companies.Keyword_Text IS NULL AND Companies.Company_id IN (SELECT company_Id FROM company_activity WHERE Date_Entered >= '9/1/2012' AND act_type = 'ADDCOM')

To find companies entered since ‘date’:

Companies.Company_id IN (SELECT Company_Id FROM company_activity WHERE Date_Entered >= '11/1/2007' AND act_type = 'ADDCOM')

Companies entered by ‘xxx’ user in the past ‘x’ days:

Companies.company_id IN (SELECT company_id FROM company_activity WHERE date_entered [GE] #[[-x day]]# AND act_type = 'ADDCOM') AND user_name = 'xxx'

To find companies with an Activity written since ‘date’

Companies.Company_id IN (SELECT Company_Id FROM company_activity WHERE Last_Activity >= '11/1/2007')

To find companies with Associations:

Companies.Company_id IN (SELECT company_id FROM Competitors WHERE Description IS NOT NULL)

To find companies with Associations that have a specific description (replace the XXXXX with the description):

Companies.Company_id IN (SELECT company_id FROM Competitors WHERE Description = 'XXXXXX')

To find companies with interview records based on written by AND interview status:

Companies.Company_id IN (SELECT Company_Id FROM sendout_records WHERE feerange_text = 'Face 1' OR feerange_text = 'Tel 1' AND written_by = 'ADMIN')

Name SQL Search Statements

To find all names entered today (all users):

Candidates.date_entered [GE] #[[-1 days]]#

To find new Candidates entered this week:

(Candidates.Specialty_2 LIKE 'c%') AND Date_entered [GE] #[[-7 days]]#

To find all names entered in past X days AND a specific value in a custom field:

Candidates.candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE entry_date [GE] #[[-90 days]]#) AND candidates.candidate_id IN (SELECT candidate_id FROM candidate_fields WHERE Fname = 'XXX' AND Ftext LIKE 'XXX%')

To find all names entered today with the status of ‘candidate’:

Candidates.date_entered [GE] #[[-1 days]]# AND Specialty_2 = 'C'

To find all names entered today with the status of ‘hiring authority’:

Candidates.date_entered [GE] #[[-1 days]]# AND Specialty_2 = 'H'

To find all names UPDATED by PCR Capture in the past 3 days:

Candidates.candidate_id IN (SELECT candidate_id FROM record_archive WHERE candidate_id=record_id AND Archive_Type='CAPTURE' AND date_changed [GE] #[[-3days]]#)

To find all names WHERE a specific field was UPDATED by PCR Capture in the past 3 days:

Candidates.candidate_id IN (SELECT candidate_id FROM record_archive WHERE candidate_id=record_id AND field_name='city' AND Archive_Type='CAPTURE' AND date_changed [GE] #[[-3days]]#)

To find all names UPDATED by PCR Capture by a specific user in the past 3 days:

Candidates.candidate_id IN (SELECT candidate_id FROM record_archive WHERE candidate_id=record_id AND Archive_Type='CAPTURE' AND User_Name='ADMIN' AND date_changed [GE] #[[-3days]]#)

To find all names created today with the status of ‘unverified’ (names created today via the Inhaler):

Candidates.date_entered [GE] #[[-1 days]]# AND Specialty_2 = 'U'

To find all names with the status of ‘unverified’ (names created via the Inhaler to be verified):

Candidates.Specialty_2 = 'U'

To find all names created today by a specific user with the status of ‘unverified’ (names created today via the Inhaler):

Candidates.date_entered [GE] #[[-1 days]]# AND Specialty_2 = 'U' AND user_name = 'yourusernamehere'

To find all names with your User Name AND the Status of ‘unverified’ (names you created via the Inhaler to be verified):

Candidates.specialty_2 = 'U' AND user_name = 'yourusernamehere'

To find all names with the Status of ‘Hiring Authority’ (H) AND associated with a ‘Client Company’:

Candidates.specialty_2 = 'H' AND company_id IN (SELECT company_id FROM Companies WHERE DefaultCo = 'X')

To find all names associated with a particular company (i.e. Main Sequence) AND your User Name:

Candidates.candidate_id IN (SELECT candidate_id FROM candidates WHERE company_id IN (SELECT company_id FROM companies WHERE company_name = 'main sequence') AND user_name = 'yourusernamehere')

To find all names who have modified their information via web extensions in past 7 days:

candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE act_type = 'SAVENAM' AND user_name ='CANDIDATE' AND memo like 'Name was modified%' AND entry_date [GE] #[[-7days]]#)

To find all names WHERE the resume has been modified in x days:

candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE Act_type = 'RESUME' AND Entry_Date [GE] #[[-X days]]#)

To find all names with blinded resumes:

Candidate_id IN (SELECT candidate_id FROM scripts WHERE script_type = 'blind')

To find names with Notes:

candidate_id IN (SELECT candidate_id FROM candidates WHERE freeform_text is not null)

To find records that do not have Keywords:

Keyword_Text IS NULL

To find records based on Keywords (Note: Keyword searching FROM the MyPCR screen will be slower than normal):

Keyword_Text like '%engineer%' Finds records with engineer
Keyword_Text like '%engineer%' AND keyword_text like '%mechanical%' Finds records with engineer AND mechanical.
keyword_text like '%mechanical engineer%' Finds records with the phrase 'mechanical engineer'.

To find names with a resume:

Resume = 'Y'

To find names with no resume:

Resume IS NULL

To find names with Associations:

Candidate_id IN (SELECT company_id FROM Competitors WHERE Description is not null)

To find names with Associations that have a specific description (replace the XXXXX with the description):

candidate_id IN (SELECT company_id FROM Competitors WHERE Description = 'XXXXXX')

To find names with profiles:

Candidate_id IN (SELECT Candidate_Id FROM Scripts WHERE Rollup_Name like 'CAPROFILE%')

To find names with profile with ‘xx’ name:

Candidate_id IN (SELECT Candidate_Id FROM Scripts WHERE Rollup_Name = 'CAPROFILE2' AND Description = 'XXX')

To find names with attachments:

Candidate_id IN (SELECT Candidate_Id FROM Scripts WHERE Rollup_Name = 'UPLOAD')

To find all names WHERE a new attachment has been added in x days:

candidate_id IN (SELECT candidate_id FROM Scripts WHERE (Rollup_Name = 'UPLOAD' OR rollup_name = 'RTF' OR rollup_name = 'LINK' OR rollup_name = 'HTML'' OR rollup_name = 'PIN' OR rollup_name = 'CAPROFILE2' OR Rollup_name = 'CAPROFILE' AND script_date [GE] #[[-X days]]#)

To find names with attachment with ‘xx’ name:

Candidate_id IN (SELECT Candidate_Id FROM Scripts WHERE Rollup_Name = 'UPLOAD' AND Description = 'XXX')

To find all names WHERE a feedback form has been received in last x days:

candidate_id IN (SELECT candidate_id FROM Scripts WHERE Rollup_Name = 'FEEDBACK' AND script_date [GE] #[[-X days]]#)

To find all names WHERE there is an activity that has a specific activity type OR result:

candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE Act_type = 'XXXX')
candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE Result = 'XXXX')

To find all names based on activity text:

candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE memo like 'XXX')

To find all names WHERE there is an activity of a specific activity type OR result written within X days:

candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE Act_type = 'XXXX' AND Entry_Date[GE] #[[-X days]]#) OR 
candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE Result = 'XXXX' AND Entry_Date [GE] #[[-X days]]#)

To find all names on a specific rollup with the status of C (closed):

candidates.candidate_id IN (SELECT candidate_id FROM rollup_records WHERE rollup_name = 'ADMIN.0001' AND description = 'C')

To find all names placed on any Name Rollup in the past 7 days:

Candidate_id IN (SELECT Candidate_Id FROM Rollup_Records WHERE Date_Created [GE] #[[-7days]]#)

To find all names added to a specific rollup in the past 7 days:

Candidate_id IN (SELECT Candidate_Id FROM Rollup_Records WHERE rollup_name='ADMIN.1234' AND Date_Created [GE] #[[-7days]]#)

To find all names placed on Rollups designated with category ‘xxx’:

(Candidates.Candidate_id IN (SELECT Candidate_Id FROM Rollup_Records WHERE Rollup_Name IN (SELECT code FROM rollup_names WHERE Category='XXX')))

To find all names not on Rollups:

Candidates.candidate_id NOT IN (SELECT candidate_id FROM rollup_records)

To find all names linked to positions:

Candidates.Candidate_id IN (SELECT candidate_id FROM sendout_records)

To find all names that have been connected to multiple jobs:

Candidates.Candidate_id IN (SELECT Candidate_id FROM Sendout_records GROUP BY Candidate_id Having Count(Distinct(job_id)) > 1 )

To find all names that have been connected to multiple AVAILABLE jobs:

Candidates.Candidate_id IN (SELECT Candidate_id FROM Sendout_records WHERE job_id IN (SELECT job_id FROM positions WHERE status ='A')
GROUP BY Candidate_id HAVING Count(Distinct(job_id)) > 1 )

To find all New names (Current Interview Type = Presentation) in pipelines based on Position User Name:

Candidates.candidate_id IN ( SELECT candidate_id FROM sendout_records WHERE Current_Stage = 5 AND Job_id IN (SELECT job_id FROM positions WHERE User_Name = 'yourusername))

To find names based on current interview status for available positions with a specific company:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE current_status = 'Submitted AND job_id IN (SELECT job_id FROM positions WHERE Status = 'A' AND company_id IN (SELECT company_id FROM companies WHERE company_name ='Main Sequence')))

To find all names in the pipeline for an Available/Open position, except those who are currently at the ‘Offer Declined’ OR ‘Out of Process’ stage.

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE current_stage = 0 OR current_stage = 1 OR current_stage = 2 OR current_stage = 3 OR current_stage = 4 OR current_stage = 5 OR current_stage = 7 OR current_stage = 8 OR current_stage = 9 AND Job_id IN (SELECT job_id FROM positions WHERE Status = 'A'))

To find all names not linked to positions:

Candidates.candidate_id NOT IN (SELECT candidate_id FROM sendout_records)

To find all names linked to positions of a specific user:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE job_id IN (SELECT Job_id FROM positions WHERE User_Name = 'usernamehere'))

To find all names linked to a position of a specific title:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE job_id IN (SELECT Job_id FROM positions WHERE Job_title = 'MANAGER'))

To find all New names in all pipelines:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE current_status = 'LINKNAME' OR current_status = 'On-Line Job Inquiry' AND interview_type = 5)

To find all names with the current interview status of ‘On-line Job Inquiry’:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE current_status = 'On-Line Job Inquiry')

To find all names with the current interview status of ‘On-line Job Inquiry’ for Available/Open positions:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE current_status = 'On-Line Job Inquiry' AND Job_id IN (SELECT job_id FROM positions WHERE Status = 'A'))

To find all names with an ‘On-line Job Inquiry’ sendout record created in the past day:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE arranged_date [GE] #[[-1 days]]# AND arranged_date [LE] #[[NOW]]# AND Feerange_Text = 'On-Line Job Inquiry')

To find all names linked to a specific job ID WHERE with the current interview status of ‘On-Line Job Inquiry’:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE current_status = 'On-Line Job Inquiry' AND Job_id IN (SELECT job_id FROM positions WHERE Position_ID = '13'))

To find all names in pipelines WHERE the Position Status is ‘Available’:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE job_id IN (SELECT job_id FROM positions WHERE status like 'A'))

To find all names with an interview written by user ‘xxx’ AND arranged date is in past ‘x’ days :

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE arranged_date [GE] #[[-x days]]# AND arranged_date [LE] #[[NOW]]# AND Written_By = 'xxx')

To find all names with a Telephone OR InPerson Interviews scheduled for next 7 days:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE appointment_date [GE] #[[NOW]]# AND appointment_date [LE] #[[7 days]]# AND Written_By = 'admin' AND (Interview_Type = '1' OR Interview_Type = '2'))

To find all names with an interview written by user ‘xxx’ AND appointment date is in past ‘x’ days :

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE appointment_date [GE] #[[-x days]]# AND appointment_date [LE] #[[NOW]]# AND Written_By = 'xxx')

To find all names currently at the Offer Stage, Offer Made OR Offer Accepted stage WHERE the Position Status is ‘Available/Open:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE current_stage = 7 OR current_stage = 8 OR current_stage = 9 AND Job_id IN (SELECT job_id FROM positions WHERE Status = 'A'))

To find all permanent hires placed by ‘xxx’ AND placement date in past ‘x’ days :

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE arranged_date [GE] #[[-x days]]# AND arranged_date [LE] #[[NOW]]# AND Written_By = 'xxx' AND Interview_Type = '4')

To find all contractors placed by ‘admin’ with contract end date in the next 30 days:

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE end_date [GE] #[[NOW]]# AND end_date [LE] #[[30 days]]# AND Written_By = 'admin' AND Interview_Type = '0')

To find all permanent hires placed by ‘xxx’ AND start date in past ‘x’ days :

Candidates.candidate_id IN (SELECT candidate_id FROM sendout_records WHERE appointment_date [GE] #[[-x days]]# AND appointment_date [LE] #[[NOW]]# AND Written_By = 'xxx' AND Interview_Type = '4')

To find all names previously placed on a contract assignment WHERE the end date has expired, AND not currently on an assignment.

Candidates.specialty_2 NOT LIKE 'a' AND candidate_id IN (SELECT candidate_id FROM sendout_records WHERE end_date [LE] #[[NOW]]# AND Interview_Type = '0')

To find names with specific email addresses in Custom Email Fields:

Candidates.Candidate_id IN (SELECT Candidate_id FROM candidate_fields WHERE Ftext IN ('email address','second email address))

Position SQL Search Statements

To find all positions entered by ‘xxx’ user in the past ‘x’ days:

Positions.job_id IN (SELECT job_id WHERE Datediff(dd,convert(datetime,Date_Posted,101),convert(datetime,GETDATE(),101))<30) AND User_Name = 'XXX'

To find all positions entered today (all users):

Positions.job_id IN (SELECT job_id FROM positions WHERE date_posted [GE] #[[-1 days]]#)

To find all available positions (all users):

Positions.Status = 'A'

To find all pending positions (all users):

Positions.Status = 'P'

To find all positions posted via web extensions (employers) with the status of Pending:

Positions.Status = 'P' AND user_Name = 'EMPLOYER'

To find all positions showing in candidate web extensions (Status=Available AND Show On Web=Show):

Positions.Status = 'A' AND Web = '0'

To find all positions posted today via web extensions (employers):

Positions.job_id IN (SELECT job_id FROM positions WHERE user_name like 'employer' AND date_posted [GE] #[[-1 day]]#)

To find the 10 most recent positions (all users):

Positions.Job_id IN (SELECT TOP 10 po.Job_Id FROM Positions po ORDER BY po.Date_Posted Desc)

To find all positions open for over 30 days (all users):

Positions.job_id IN (SELECT job_id FROM positions WHERE date_posted [LE] #[[-30 days]]# AND status = 'A')

To find all positions opened in the past 3 days (all users):

Positions.job_id IN (SELECT job_id FROM positions WHERE date_posted [GE] #[[-3 days]]# AND status = 'A')

To find Available/Open positions that currently have Telephone OR In-Person interview records:

Positions.job_id IN (SELECT job_id FROM sendout_records WHERE current_stage = '1' OR current_stage = '2') AND status = 'A'

To find Available/Open positions that currently do not have Telephone OR In-Person interview records:

Positions.job_id NOT IN (SELECT job_id FROM sendout_records WHERE current_stage = '1' OR current_stage = '2') AND status = 'A'

To find Available/Open positions that do not have an interview record with the interview type of ‘Resume’:

Positions.job_id not in (SELECT job_id FROM sendout_records WHERE interview_type = '3') AND status='A'

To find positions based on user name that an employer has edited via the web extensions in the past day. (Replace admin with the correct user name.)

Positions.user_name = 'admin' AND job_id IN (SELECT position_id FROM Position_Activities WHERE user_name = 'employer' AND act_type = 'savepos' AND Date_Entered [GE] #[[-1 days]]#)

Support Center