Photo of Hunter Goolsbey


Room Management Add-In tool: Querying Start Date - Issues

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:


SET @i = -1

WHILE @i <= 52


Select WeeklyDayOfWeek, EffectiveStartDate,


   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



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,


  • Photo of Sarah Boota


    Hi Hunter, 

    Sarah Boota on behalf of BEMA Software services here.  There's not really a clean way to get occurrence dates in SQL because there are no tools in SQL to loop through the calendar string.

    We'd recommend, if you're workflows, to use the GetReservationOccurrences API call in a Web Request API action, and loop through the results. Both BEMA room reservation versions should have this.


    Sarah Boota