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())) >= 1Filter 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 = 1Techniques
- COUNT SQL Function
- GETDATE SQL Function