Variations

Without Milestones

Add the NOT operator to filter for anyone who does not have the Milestone:

NOT EXISTS (SELECT 1 FROM Participant_Milestones PS WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3 )

Multiple Milestones: Any

The highlighted portion of the basic criteria may be changed from a single Milestone ID or a list of Group ID like the following. This would show all Contacts who have at least one of the Milestones:

EXISTS (SELECT 1 FROM Participant_Milestones PS WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID IN (3, 4, 18) )

Multiple Milestones: All

To only show contacts who have all the Milestones in a list, they need to be broken out separately. This example would show all Contacts who have all three of the Milestones:

EXISTS (SELECT 1 FROM Participant_Milestones PS   
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3 )
AND EXISTS (SELECT 1 FROM Participant_Milestones PS   
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 4 )
AND EXISTS (SELECT 1 FROM Participant_Milestones PS   
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 18 )

Milestone Dates

The date accomplished may be added to the criteria like this to limit the results.

Milestone Accomplished In a Date Range

Here is how to filter for Date Accomplished from January 1, 2015 to December 31, 2015. Dates are entered in single quotes and written in the format YYYY-MM-DD.

EXISTS (SELECT 1 FROM Participant_Milestones PS WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3 AND PS.Date_Accomplished BETWEEN '2015-01-01' AND '2015-12-31' )

Milestone Accomplished in Past 60 Days

The following criteria show how to filter by a date in the past 60 days using the GETDATE() function.

EXISTS (SELECT 1 FROM Participant_Milestones PS 
WHERE PS.Participant_ID = Contacts.Participant_Record 
AND PS.Milestone_ID = 3 AND PS.Date_Accomplished > GETDATE()-60 )

Milestones Fields

Milestone Date Field

This field displays the date a Milestone was accomplished.

,(SELECT PS.Date_Accomplished FROM Participant_Milestones PS   WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3) AS [Baptism Date]Copied

Has Milestone Field

This field displays "Yes" or "No" depending if the Contact has the "Baptized" Milestone or not:

,CASE WHEN EXISTS(SELECT 1 FROM Participant_Milestones PS  
WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3)  
THEN 'Yes' ELSE 'No' END AS [Baptized]

Participants With Milestones

This View can easily be changed to work on the Participants page. The Participant_Milestones Participant_ID needs to be compared to the Participant_ID field rather than the Contact Participant_Record field. The criteria looks like this. The highlighted portion is the part that needs to be altered:

EXISTS (SELECT 1 FROM Participant_Milestones PS WHERE PS.Participant_ID = Participants.Participant_ID  AND PS.Milestone_ID = 3)

Techniques

Features: Correlated Subquery

The criteria utilizes EXISTS to check for matching records. The Contact's Participant_Milestone field is inserted to make this a correlated subquery. GETDATE can be used to check that the date accomplished is within a particular timeframe.