Contains a quick view of Target Size, Group member count and available space

Page: Groups

Fields

Count of Current Members

(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

Count of Open Slots

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

Complete View

Use this view as a filtered page or as a simple view on the Groups page.

Field List

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]

View Clause

(Groups.End_Date IS NULL OR Groups.End_Date >= GETDATE()) AND Groups.Group_Type_ID = 1