Contacts with Milestones
This View lists all Contacts with a particular Milestone. This can be altered to filter for a list of Milestones.
Difficulty: Intermediate
Description: Contacts who have a Milestone Assigned.
Page: Contacts
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 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
You can change the basic criteria 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
You can change this View 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:
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.