CONTACT USBack to MinistryPlatform Site
Your Knowledge Base has moved to the new Help Center.  Check out the release notes for details. And don't forget to update your bookmarks and in-house documentation before May 28.

Groups Under Capacity

 

This view shows Groups that under their target group size.

Difficulty: Intermediate
Purpose: Creates a view on the Groups page that will show any Group where the active participants in the Group is under the Target Group Size.

Page: Groups

Field List

[Group_Name] AS [Group Name] 
, [Target_Size] AS [Target Size] 
, (SELECT Count(*) FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID 
AND GetDate() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,Getdate())) AS Current_Participants 
, ISNULL(Groups.Target_Size,0)-(SELECT Count(*) 
FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID 
AND GetDate() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,Getdate())) AS Space_Available 
, Group_Type_ID_Table.[Group_Type] AS [Group Type] 
, [End_Date] AS [End Date]
, [Group_is_Full] AS [Group Full]

View Clause

[End_Date] IS NULL 
AND ISNULL(Groups.Target_Size,0)-(SELECT Count(*) 
FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID 
AND GetDate() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,Getdate())) >= 1

Variations

Filter for a specific Group Type

To only list Groups in a specific Group Type you can add this criteria (substitute the appropriate Group Type ID for the number below:

Groups.Group_Type_ID = 1
Techniques