Add Repeated Event Reservations By Weekday
The Script below/attached will allow you to add repeated Event Reservations to the Event Calendar. The Event will not appear in the venue until the user clicks on the Event Reservation and Creates it manually.
I have changed the text color to red of the portion of the script you will be modifying.
What will you be changing on the script?
SET @StartDate = '2019-08-01' -- set to the date of when it starts
SET @EndDate = '2019-08-25' -- set to the date to when it ends
SET @StartTime = '09:30:00' -- set start time of when it starts
SET @EndTime = '11:00:00' -- set end time of when it ends
SET @Subject = 'Cadet Club Sunday Morning' -- This will name of Event Reservation
SET @Description = 'description' -- This will set the description as description
SET @EventTypeID = 4 -- The Event Type ID you can get this from Sp_admin or dbo.eventreservationtype
SET @TypeID = 1 -- What track will it be on you can also check dbo.EventReservationTypes
SET @EventDay = 1 -- 1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thursday, 6:Friday, 7:Saturday, the day you need repeat
Please consult with Level 2 and Management before running.
-------------------------------------------------------------------------------------------------------------------
DECLARE @StartDate DATE;
DECLARE @EndDate DATE;
DECLARE @StartTime TIME;
DECLARE @EndTime TIME;
DECLARE @Subject nvarchar(255)
DECLARE @Description nvarchar(max)
DECLARE @EventTypeID INT
DECLARE @TypeID INT
DECLARE @EventDay INT
SET @StartDate = '2019-08-01'
SET @EndDate = '2019-08-25'
SET @StartTime = '09:30:00'
SET @EndTime = '11:00:00'
SET @Subject = 'Cadet Club Sunday Morning'
SET @Description = 'description'
SET @EventTypeID = 4
SET @TypeID = 1
SET @EventDay = 1
;WITH DAY_CTE(dt) AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(d, 1, dt) FROM DAY_CTE
WHERE dt < @EndDate
),
DATETIMES_CTE AS (
SELECT
CAST(d.dt AS DATETIME) + CAST(@StartTime AS DATETIME) AS 'StartDateTime',
CAST(d.dt AS DATETIME) + CAST(@EndTime AS DATETIME) AS 'EndDateTime'
FROM DAY_CTE d
)
INSERT INTO [ClubspeedV8].[dbo].[EventReservations] (
StartTime,
EndTime,
Subject,
Description,
EventTypeID,
TypeID,
IsEventClosure,
Status,
Label
)
SELECT
dt.StartDateTime,
dt.EndDateTime,
@Subject,
@Description,
@EventTypeID,
@TypeID,
0,
0,
0
FROM DATETIMES_CTE dt
WHERE DATEPART(dw, dt.StartDateTime) = @EventDay