Hello Rock Community!

I am looking for more information on the RoomManagement add-in, specifically in the area of querying the Scheduled start date for a given occurrence in a reoccurring schedule.

To preface a little, my code will go into a workflow that will eventually query a "Daily" and "Tomorrow's" report for our facilities team.

Recently I have used "EffectiveStartDate" but this only stores the first start date of a reoccurring event. (EX: if weekly art class begins this thursday, querying EffectiveStartDate for that following thursday, returns the first instance only.

My goal is to query the Individual Start Date for each reoccurring-scheduled-item.

To work around this, I have tried using the following SQL code:

DECLARE @i INT

SET @i = -1

WHILE @i <= 52

BEGIN

Select WeeklyDayOfWeek, EffectiveStartDate,

CASE

   WHEN Schedule.WeeklyDayOfWeek IS NOT null

    THEN CAST(DATEADD(day, -7*@i, CAST(GETDATE() as Date)) as Date)

    ELSE CAST(Schedule.EffectiveStartDate as Date)

    END EventStart

INTO #TempTable

FROM Schedule

SET @i = @i + 1

DROP TABLE IF EXISTS #TempTable

END

My desire is to iterate through weekly-occurring events, by subtracting 7 from the current date.

Is there a better solution out there to replace a While(If()) nested statement in SQL?

Or, would it be more time saving to create a new table within the DB to begin importing those dates and call them directly from a stored table?

Thank you in advance for your help,

-Hunter