Group Counts
Contains a quick view of Target Size, Group member count and available space.
Difficulty: Advanced
Purpose: Creates a view that contains a quick view of Target Size, Current Group members, 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_ParticipantsCount 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_AvailableComplete 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