Recipe - Reference for Sign-Ups

Skill level: Advanced

Organization: Consta Tech

Requires Rock: 1.15.0

{# strip images & classes from the HTML but otherwise leave structure #} Table of Contents:

The five screenshots

Hello 👋 in the past two years, around volunteer-heavy times, i noticed that the General, Lava, and SQL channels normally ask questions regarding the data models behind Sign-Ups (Sign-Up Groups, Sign-Up Opportunities, etc). i hope that these five screenshots are as helpful to you as they were to me:

Some example queries

Persons who signed up

Input: the Id of the Sign-Up Group, and the Id of a ScheduleCategory.
Output: PersonId of everyone who signed-up to that [Group], for an upcoming [Schedule] in that ScheduleCategory.

DECLARE @SignUpGroupId int = 999999;
DECLARE @ScheduleCategoryId int = 999999;

SELECT DISTINCT
    gm.[PersonId]
FROM
    [GroupMember] gm
    INNER JOIN [GroupMemberAssignment] gma ON gma.[GroupMemberId] = gm.[Id]
    INNER JOIN [Schedule] sch ON sch.[Id] = gma.[ScheduleId]
WHERE
    gm.[GroupId] = @SignUpGroupId
    AND
    sch.[CategoryId] = @ScheduleCategoryId
    AND
    sch.[IsActive] = 1
;

Schedules with available Slots

Input: the Id of the Sign-Up Group
Output: values for Single-Select (or Multi-Select) where User can select ScheduleId (or ScheduleIds)

DECLARE @SignUpGroupId int = 999999;

DECLARE @Opportunity table (
    [GroupId] int NOT NULL
  , [LocationId] int NOT NULL
  , [ScheduleId] int NOT NULL
  , [ScheduleName] nvarchar(100) NOT NULL
  , [ProjectName] nvarchar(100) NOT NULL
  , [OpportunityName] nvarchar(100) NULL
  , [EffectiveStartDate] date NULL
  , [EffectiveEndDate] date NULL
  , [iCalendarContent] nvarchar(max)
  , [SlotsMin] int NULL
  , [SlotsDesired] int NULL
  , [SlotsMax] int NULL
  , [ParticipantCount] int NOT NULL
);

WITH CTE AS (
    SELECT
        g.[Id] AS "GroupId"
      , gl.[LocationId] AS "LocationId"
      , glsc.[ScheduleId] AS "ScheduleId"
      , sch.[Name] AS "ScheduleName"
      , g.[Name] AS "ProjectName"
      , glsc.[ConfigurationName] AS "OpportunityName"
      , sch."EffectiveStartDate"
      , sch."EffectiveEndDate"
      , sch."iCalendarContent"
      , glsc.[MinimumCapacity] AS "SlotsMin"
      , glsc.[DesiredCapacity] AS "SlotsDesired"
      , glsc.[MaximumCapacity] AS "SlotsMax"
    FROM
        [GroupLocation] gl
        INNER JOIN [Group] g ON g.[Id] = gl.[GroupId]
        INNER JOIN [GroupType] gt ON gt.[Id] = g.[GroupTypeId]
        INNER JOIN [GroupLocationScheduleConfig] glsc ON glsc.[GroupLocationId] = gl.[Id]
        INNER JOIN [Schedule] sch ON sch.[Id] = glsc.[ScheduleId]
    WHERE
        g.[Id] = @SignUpGroupId
        AND
        sch.[IsActive] = 1
)
INSERT INTO @Opportunity
SELECT
    CTE.*
  , COUNT(gma.[Id]) AS "ParticipantCount"
FROM CTE
    LEFT OUTER JOIN [GroupMember] gm ON gm.[GroupId] = CTE.[GroupId]
    LEFT OUTER JOIN [GroupMemberAssignment] gma ON gma.[GroupMemberId] = gm.[Id] AND gma.[LocationId] = CTE.[LocationId] AND gma.[ScheduleId] = CTE.[ScheduleId]
GROUP BY
    CTE.[GroupId]
  , CTE.[LocationId]
  , CTE.[ScheduleId]
  , CTE.[ScheduleName]
  , CTE.[ProjectName]
  , CTE.[OpportunityName]
  , CTE.[EffectiveStartDate]
  , CTE.[EffectiveEndDate]
  , CTE.[iCalendarContent]
  , CTE.[SlotsMin]
  , CTE.[SlotsDesired]
  , CTE.[SlotsMax]
ORDER BY
    CTE.[EffectiveStartDate] ASC
  , CTE.[ProjectName] ASC
  , [ParticipantCount] DESC
;

SELECT
    [ScheduleId] AS "Value"
  , [ScheduleName] AS "Text"
  , MIN([EffectiveStartDate]) AS "SortDate"
FROM
    @Opportunity
GROUP BY
    [ScheduleId]
  , [ScheduleName]
HAVING
    SUM(CASE WHEN [SlotsMax] IS NOT NULL AND [SlotsMax] > 0 THEN [SlotsMax] ELSE 0 END - [ParticipantCount]) > 0
ORDER BY
    [SortDate] ASC
  , [ScheduleName] ASC
;