Contains a quick view of Target Size, Group member count and available space
Page: Groups
(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
Use this view as a filtered page or as a simple view on the Groups page.
Groups.[Group_Name] AS [Group Name] , Congregation_ID_Table.[Congregation_Name] AS [Group Congregation] , Parent_Group_Table.[Group_Name] AS [Neighborhood] , Life_Stage_ID_Table.[Life_Stage] AS [Life Stage] , Meeting_Day_ID_Table.[Meeting_Day] AS [Meeting Day] , Groups.[Meeting_Time] AS [Meeting Time] , Groups.[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 , Groups.[Group_Is_Full] AS [Group_is_Full]
(Groups.End_Date IS NULL OR Groups.End_Date >= GETDATE()) AND Groups.Group_Type_ID = 1