CONTACT USBack to MinistryPlatform Site
Your Knowledge Base is moving on 3/25/24! Our new Help Center provides all the articles you know and love (plus so much more) in a one-stop shop. Ask your SPoC for details!

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