Upcoming Service Times in a Persisted Dataset

Shared by Jessica Gorbett, 9 Embers
4 days ago

17.9 CMS Intermediate

One of the advantages of hosting your website in Rock is the ability to create a single source of truth. Instead of manually updating service times across your website and mobile app, you can use the campus schedules already managed in Rock.

This recipe creates a reusable Persisted Dataset that returns upcoming campus schedule occurrences as JSON. From there, you can use the data to power schedule-driven content throughout your Rock environment.

How to Set It Up

1. Review Your Campus Schedules

Make sure your schedules are associated with their respective campuses in Rock. Ensure duration is set for each schedule as well.

2. Create a Persisted Dataset

Navigate to Admin Tools > CMS Configuration > Persisted Datasets and create a new dataset.

Set Access Key: campusscheduleoccurrences

Enabled Lava Commands: SQL

Consider how frequently schedules change when determining your refresh schedule and cache duration.

3. Add the Build Script

Paste the Campus Schedule Occurrences Lava/SQL script into the Build Script field.

The script returns upcoming schedule occurrences for active campuses over the next 7 days as a JSON array.

{%- sql return:'rows' -%}

DECLARE @Now datetime2 = GETDATE();
DECLARE @Cutoff datetime2 = DATEADD(DAY, 7, @Now);

;WITH DayOffsets AS
(
    SELECT [DayOffset]
    FROM (VALUES
        (-1), (0), (1), (2), (3),
        (4), (5), (6), (7)
    ) AS days([DayOffset])
),

CampusSchedules AS
(
    SELECT
        c.[Id] AS [CampusId],
        c.[Name] AS [CampusName],
        dvStatus.[Value] AS [CampusStatus],

        cs.[Order] AS [CampusScheduleOrder],
        dvType.[Value] AS [CampusScheduleType],

        s.[Id] AS [ScheduleId],
        s.[Name] AS [ScheduleName],
        CAST(s.[IsActive] AS int) AS [ScheduleIsActive],

        REPLACE(
            REPLACE(
                s.[iCalendarContent],
                CHAR(13), ''
            ),
            CHAR(10), '|'
        ) AS [ICal]

    FROM [Campus] c

    INNER JOIN [CampusSchedule] cs
        ON cs.[CampusId] = c.[Id]

    INNER JOIN [Schedule] s
        ON s.[Id] = cs.[ScheduleId]

    LEFT JOIN [DefinedValue] dvStatus
        ON dvStatus.[Id] = c.[CampusStatusValueId]

    LEFT JOIN [DefinedValue] dvType
        ON dvType.[Id] = cs.[ScheduleTypeValueId]

    WHERE c.[IsActive] = 1
),

ICalTokens AS
(
    SELECT
        *,

        CASE
            WHEN CHARINDEX('DTSTART:', [ICal]) > 0
            THEN SUBSTRING(
                [ICal],
                CHARINDEX('DTSTART:', [ICal]) + 8,
                15
            )
        END AS [StartText],

        CASE
            WHEN CHARINDEX('DTEND:', [ICal]) > 0
            THEN SUBSTRING(
                [ICal],
                CHARINDEX('DTEND:', [ICal]) + 6,
                15
            )
        END AS [EndText],

        CASE
            WHEN CHARINDEX('BYDAY=', [ICal]) > 0
            THEN LEFT(
                SUBSTRING(
                    [ICal],
                    CHARINDEX('BYDAY=', [ICal]) + 6,
                    100
                ),
                CHARINDEX(
                    '|',
                    SUBSTRING(
                        [ICal],
                        CHARINDEX('BYDAY=', [ICal]) + 6,
                        100
                    ) + '|'
                ) - 1
            )
        END AS [ByDayLine],

        CASE
            WHEN CHARINDEX('EXDATE:', [ICal]) > 0
            THEN LEFT(
                SUBSTRING(
                    [ICal],
                    CHARINDEX('EXDATE:', [ICal]) + 7,
                    500
                ),
                CHARINDEX(
                    '|',
                    SUBSTRING(
                        [ICal],
                        CHARINDEX('EXDATE:', [ICal]) + 7,
                        500
                    ) + '|'
                ) - 1
            )
            ELSE ''
        END AS [ExDateText],

        CASE
            WHEN CHARINDEX('UNTIL=', [ICal]) > 0
            THEN SUBSTRING(
                [ICal],
                CHARINDEX('UNTIL=', [ICal]) + 6,
                8
            )
        END AS [UntilText],

        CASE
            WHEN CHARINDEX('RRULE:', [ICal]) = 0
                THEN 'Once'
            WHEN CHARINDEX('FREQ=WEEKLY', [ICal]) > 0
                THEN 'Weekly'
            WHEN CHARINDEX('FREQ=DAILY', [ICal]) > 0
                THEN 'Daily'
            ELSE 'Unsupported'
        END AS [RecurrenceType],

        CASE
            WHEN CHARINDEX('INTERVAL=', [ICal]) = 0
                THEN 1
            WHEN CHARINDEX('INTERVAL=1;', [ICal]) > 0
                THEN 1
            WHEN CHARINDEX('INTERVAL=1|', [ICal]) > 0
                THEN 1
            ELSE 0
        END AS [IsSupportedInterval],

        CASE
            WHEN CHARINDEX('COUNT=', [ICal]) > 0
                THEN 1
            ELSE 0
        END AS [HasCountLimit]

    FROM CampusSchedules

    WHERE NULLIF([ICal], '') IS NOT NULL
),

ParsedICal AS
(
    SELECT
        *,

        LEFT(
            [ByDayLine],
            CHARINDEX(';', [ByDayLine] + ';') - 1
        ) AS [ByDay],

        TRY_CONVERT(
            date,
            STUFF(
                STUFF([UntilText], 5, 0, '-'),
                8, 0, '-'
            )
        ) AS [UntilDate],

        TRY_CONVERT(
            datetime2,
            STUFF(
                STUFF(
                    LEFT([StartText], 8),
                    5, 0, '-'
                ),
                8, 0, '-'
            )
            + ' ' +
            STUFF(
                STUFF(
                    SUBSTRING([StartText], 10, 6),
                    3, 0, ':'
                ),
                6, 0, ':'
            )
        ) AS [OriginalStartDateTime],

        TRY_CONVERT(
            datetime2,
            STUFF(
                STUFF(
                    LEFT([EndText], 8),
                    5, 0, '-'
                ),
                8, 0, '-'
            )
            + ' ' +
            STUFF(
                STUFF(
                    SUBSTRING([EndText], 10, 6),
                    3, 0, ':'
                ),
                6, 0, ':'
            )
        ) AS [OriginalEndDateTime]

    FROM ICalTokens
),

CandidateDates AS
(
    SELECT
        p.*,

        CAST(
            DATEADD(
                DAY,
                d.[DayOffset],
                CAST(@Now AS date)
            ) AS date
        ) AS [OccurrenceDate]

    FROM ParsedICal p

    CROSS JOIN DayOffsets d

    WHERE
        p.[OriginalStartDateTime] IS NOT NULL
        AND p.[OriginalEndDateTime] IS NOT NULL
        AND p.[RecurrenceType] <> 'Unsupported'
        AND p.[IsSupportedInterval] = 1
        AND p.[HasCountLimit] = 0
),

MatchingDates AS
(
    SELECT
        *,

        CASE
            (
                (
                    DATEDIFF(
                        DAY,
                        CAST('1900-01-07' AS date),
                        [OccurrenceDate]
                    ) % 7
                ) + 7
            ) % 7

            WHEN 0 THEN 'SU'
            WHEN 1 THEN 'MO'
            WHEN 2 THEN 'TU'
            WHEN 3 THEN 'WE'
            WHEN 4 THEN 'TH'
            WHEN 5 THEN 'FR'
            WHEN 6 THEN 'SA'
        END AS [OccurrenceDayCode]

    FROM CandidateDates
),

Occurrences AS
(
    SELECT
        *,

        DATEADD(
            SECOND,
            DATEDIFF(
                SECOND,
                CAST('00:00:00' AS time),
                CAST([OriginalStartDateTime] AS time)
            ),
            CAST([OccurrenceDate] AS datetime2)
        ) AS [OccurrenceStartDateTime]

    FROM MatchingDates

    WHERE
        [OccurrenceDate] >= CAST(
            [OriginalStartDateTime] AS date
        )

        AND (
            [UntilDate] IS NULL
            OR [OccurrenceDate] <= [UntilDate]
        )

        AND (
            (
                [RecurrenceType] = 'Once'
                AND [OccurrenceDate] = CAST(
                    [OriginalStartDateTime] AS date
                )
            )

            OR (
                [RecurrenceType] = 'Daily'
                AND [ByDay] IS NULL
            )

            OR (
                [RecurrenceType] = 'Weekly'
                AND CHARINDEX(
                    ',' + [OccurrenceDayCode] + ',',
                    ',' + ISNULL([ByDay], '') + ','
                ) > 0
            )
        )

        AND CHARINDEX(
            CONVERT(char(8), [OccurrenceDate], 112),
            ISNULL([ExDateText], '')
        ) = 0
),

FinalOccurrences AS
(
    SELECT
        *,

        DATEADD(
            SECOND,
            DATEDIFF(
                SECOND,
                [OriginalStartDateTime],
                [OriginalEndDateTime]
            ),
            [OccurrenceStartDateTime]
        ) AS [OccurrenceEndDateTime]

    FROM Occurrences
)

SELECT
    [CampusId],
    [CampusName],
    [CampusStatus],

    [CampusScheduleOrder],
    [CampusScheduleType],

    [ScheduleId],
    [ScheduleName],
    [ScheduleIsActive],

    CONVERT(
        varchar(19),
        [OccurrenceStartDateTime],
        126
    ) AS [OccurrenceStartDateTime],

    CONVERT(
        varchar(19),
        [OccurrenceEndDateTime],
        126
    ) AS [OccurrenceEndDateTime],

    CONVERT(
        varchar(10),
        [OccurrenceStartDateTime],
        23
    ) AS [OccurrenceDate],

    CONVERT(
        varchar(8),
        CAST([OccurrenceStartDateTime] AS time),
        108
    ) AS [OccurrenceTime],

    [OccurrenceDayCode] AS [DayOfWeek],

    DATEDIFF(
        MINUTE,
        [OccurrenceStartDateTime],
        [OccurrenceEndDateTime]
    ) AS [DurationMinutes]

FROM FinalOccurrences

WHERE
    [OccurrenceStartDateTime] <= @Cutoff
    AND [OccurrenceEndDateTime] >= @Now

ORDER BY
    [OccurrenceStartDateTime],
    [CampusScheduleOrder],
    [CampusName];

{%- endsql -%}

[
{%- for row in rows -%}
    {
        "CampusId": {{ row.CampusId | ToJSON }},
        "CampusName": {{ row.CampusName | ToJSON }},
        "CampusStatus": {{ row.CampusStatus | ToJSON }},
        "CampusScheduleOrder": {{ row.CampusScheduleOrder | ToJSON }},
        "CampusScheduleType": {{ row.CampusScheduleType | ToJSON }},
        "ScheduleId": {{ row.ScheduleId | ToJSON }},
        "ScheduleName": {{ row.ScheduleName | ToJSON }},
        "ScheduleIsActive": {{ row.ScheduleIsActive | ToJSON }},
        "OccurrenceStartDateTime": {{ row.OccurrenceStartDateTime | ToJSON }},
        "OccurrenceEndDateTime": {{ row.OccurrenceEndDateTime | ToJSON }},
        "OccurrenceDate": {{ row.OccurrenceDate | ToJSON }},
        "OccurrenceTime": {{ row.OccurrenceTime | ToJSON }},
        "DayOfWeek": {{ row.DayOfWeek | ToJSON }},
        "DurationMinutes": {{ row.DurationMinutes | ToJSON }}
    }{% unless forloop.last %},{% endunless %}
{%- endfor -%}
]

4. Access the Dataset

Retrieve the dataset using its Access Key. From here, you can filter the results and format them for your website or mobile app.

{% assign schedules = 'campusscheduleoccurrences' | PersistedDataset %}
{% for item in schedules %}
    <p>
        {{ item.CampusName }} —
        {{ item.ScheduleName }} —
        {{ item.OccurrenceStartDateTime }}
    </p>
{% endfor %}

Possible Extensions

Once you have a reliable source of upcoming schedule occurrences, you can build additional experiences without duplicating the schedule data.

Campus-Specific Content: Display schedules based on a visitor's selected campus or primary campus.
Livestream: Add a campus attribute for livestream URLs and update the persisted dataset.
Schedule-Based Features: Show or hide content based on an upcoming or current schedule. This could support check-in links, service reservations, or other time-sensitive actions.
Holiday and Special Services: Reflect changes to supported campus schedules without separately updating static website content.

The goal is to manage schedules once in Rock and let the experiences connected to them stay in sync.

Note: This is just a starting point. Comment below with your tweaks, extensions, adaptations, etc.

Recipe

Upcoming Service Times in a Persisted Dataset

Upcoming Service Times in a Persisted Dataset

How to Set It Up

1. Review Your Campus Schedules

2. Create a Persisted Dataset

3. Add the Build Script

4. Access the Dataset

Possible Extensions

Login to add a comment...