What would you like to know more about?

Groups Over Capacity

This view shows Groups that are at or over their Target Size.

Difficulty: Advanced

Purpose: Creates a view on the Groups page that will show any Group where the active participants in the Group matches or exceeds 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())) <= 0

Filters 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

  • COUNT SQL Function
  • GETDATE SQL Function