---
title: Common MyPCR Search Queries
description: "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](https://learning.pcrecruiter.net/site/docs/searching/advanced-search/) screens, but may require modification. *Note: The [LE], [GE], #[[-1 days]]# etc. methods are…"
url: "https://learning.pcrecruiter.net/site/docs/searching/sql-queries/"
updated: "2026-09-29"
category: Searching
---

# Common MyPCR Search Queries

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](https://learning.pcrecruiter.net/site/docs/searching/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 (e.g. 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 NULL
```

**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'.
```

**Find names with a resume:**

```
Resume = 'Y'
```

**Find names with no resume:**

```
Resume IS NULL
```

**Find 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')
OR
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:**

```
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 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 In-Person Interview 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 IN ('7','8','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.Status='A' AND Positions.job_id NOT IN (SELECT job_id FROM sendout_records WHERE current_stage = '1' OR current_stage = '2')
```

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