The following search queries can be used in the MyPCR Screen’s Search Links area. Many of these queries can also be applied within the Advanced Search screens, but may require modification.
Note: The [LE], [GE], #[[-1 days]]# etc. methods are specific to the MyPCR screen and are not standard SQL search terminology. Date-range searches in other screens typically require a specific m/d/yyyy start and end.
Company SQL Search Statements #
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')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]]#)Find companies with no names:
Companies.Company_id NOT IN (SELECT company_id FROM candidates)Find companies specified as ‘Client Company’:
Companies.DefaultCo = 'X'Find companies specified as ‘Client Company’ based on user name:
Companies.DefaultCo = 'X' AND Companies.user_name LIKE 'USERNAMEHERE'Find companies that are not on a rollup:
Companies.Company_Id NOT IN (SELECT Company_Id FROM RollupC_Records)Find companies with no positions:
Companies.company_id NOT IN (SELECT company_id FROM positions)Find companies based on username of positions:
Companies.company_id IN (SELECT company_ID FROM positions WHERE user_name = 'USERNAMEHERE')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')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'Find companies with an Activity written since ‘date’
Companies.Company_id IN (SELECT Company_Id FROM company_activity WHERE Last_Activity >= '11/1/2007')Find companies with Associations:
Companies.Company_id IN (SELECT company_id FROM Competitors WHERE Description IS NOT NULL)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')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 #
Find all names entered today (all users):
Candidates.date_entered [GE] #[[-1 days]]#Find new Candidates entered this week:
(Candidates.Specialty_2 LIKE 'c%') AND Date_entered [GE] #[[-7 days]]#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%')Find all names entered today with the status of ‘candidate’:
Candidates.date_entered [GE] #[[-1 days]]# AND Specialty_2 = 'C'Find all names entered today with the status of ‘hiring authority’:
Candidates.date_entered [GE] #[[-1 days]]# AND Specialty_2 = 'H'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]]#)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]]#)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]]#)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'Find all names with the status of ‘unverified’ (names created via the Inhaler to be verified):
Candidates.Specialty_2 = 'U'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'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'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')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')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]]#)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]]#)Find all names with blinded resumes:
Candidate_id IN (SELECT candidate_id FROM scripts WHERE script_type = 'blind')Find names with Notes:
candidate_id IN (SELECT candidate_id FROM candidates WHERE freeform_text is not null)Find records that do not have Keywords:
Keyword_Text IS NULLFind 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'.Find names with a resume:
Resume = 'Y'Find names with no resume:
Resume IS NULLFind names with Associations:
Candidate_id IN (SELECT company_id FROM Competitors WHERE Description is not null)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')Find names with profiles:
Candidate_id IN (SELECT Candidate_Id FROM Scripts WHERE Rollup_Name like 'CAPROFILE%')Find names with profile with ‘xx’ name:
Candidate_id IN (SELECT Candidate_Id FROM Scripts WHERE Rollup_Name = 'CAPROFILE2' AND Description = 'XXX')Find names with attachments:
Candidate_id IN (SELECT Candidate_Id FROM Scripts WHERE Rollup_Name = 'UPLOAD')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]]#)Find names with attachment with ‘xx’ name:
Candidate_id IN (SELECT Candidate_Id FROM Scripts WHERE Rollup_Name = 'UPLOAD' AND Description = 'XXX')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]]#)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')Find all names based on activity text:
candidate_id IN (SELECT candidate_id FROM candidate_activity WHERE memo like 'XXX')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]]#)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')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]]#)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]]#)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')))Find all names not on Rollups:
Candidates.candidate_id NOT IN (SELECT candidate_id FROM rollup_records)Find all names linked to positions:
Candidates.Candidate_id IN (SELECT candidate_id FROM sendout_records)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 )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 )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))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')))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'))Find all names not linked to positions:
Candidates.candidate_id NOT IN (SELECT candidate_id FROM sendout_records)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'))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'))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)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')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'))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')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'))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'))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')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'))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')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'))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')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')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')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')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 #
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'Find all positions entered today (all users):
Positions.job_id IN (SELECT job_id FROM positions WHERE date_posted [GE] #[[-1 days]]#)Find all available positions (all users):
Positions.Status = 'A'Find all pending positions (all users):
Positions.Status = 'P'Find all positions posted via web extensions (employers) with the status of Pending:
Positions.Status = 'P' AND user_Name = 'EMPLOYER'Find all positions showing in candidate web extensions (Status=Available AND Show On Web=Show):
Positions.Status = 'A' AND Web = '0'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]]#)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)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')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')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'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'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'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]]#)