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]]#)