This view shows Groups that are at or over their Target Size.
[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]
[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
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