This View lists all Contacts with a particular Milestone. This can be altered to filter for a list of Milestones.

The following SQL may be used for criteria. You need to find the ID associated with your Milestone.

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

Variations

Without Milestone

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 )

Milestone 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]

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.