/**** EventsGetByRange ****/

ALTER PROCEDURE {databaseOwner}[{objectQualifier}EventsGetByRange]
(
 @ModuleIDs nvarchar(255),
 @BeginDate datetime,
 @EndDate datetime,
 @CategoryName nVarchar(50)
)
AS
SET DATEFORMAT mdy

CREATE Table #EventIDs
 (EventID Int, NoOfRecurrences Int, LastRecurrence DateTime)

INSERT INTO #EventIDs (EventID, NoOfRecurrences, LastRecurrence)
SELECT DISTINCT e.EventID, Count(E2.EventID) as NoOfRecurrences, Max(E2.EventDateBegin) as LastRecurrence
 FROM {databaseOwner}[{objectQualifier}Events] E
 LEFT OUTER JOIN {databaseOwner}[{objectQualifier}EventsMaster] M ON E.ModuleID = M.SubEventID
 LEFT JOIN {databaseOwner}[{objectQualifier}Events] as E2 ON E.RecurMasterID = E2.RecurMasterID
 WHERE ((E.EventTimeBegin < DATEADD(DAY,1,@EndDate) AND DATEADD(minute,E.Duration,E.EventTimeBegin) >=  @BeginDate) OR
   (E.EventTimeBegin >= @BeginDate AND E.EventTimeBegin < DATEADD(DAY,1, @EndDate)))
   AND E.Approved = 1
   AND E.Cancelled = 0
   AND (E.ModuleID in (SELECT * FROM {databaseOwner}[{objectQualifier}SplitIDs](@ModuleIDs)))
GROUP By E.EventID

SELECT E.PortalID, E.EventID, E.RecurMasterID, E.ModuleID, E.EventDateBegin, E.EventDateEnd,
 E.EventTimeBegin, E.Duration, E.EventName, E.EventDesc,
 E.Importance, E.CreatedDate,
 CreatedBy = U.DisplayName,
 E.CreatedByID,
 E.Every,
 E.Period,
 E.RepeatType,
 E.Notify,
 E.approved,
 E.Signups,
 E.MaxEnrollment,
 (Select count(*) from {databaseOwner}[{objectQualifier}EventsSignups] WHERE EventID = E.EventID and E.Signups = 1) as Enrolled,
 ET.NoOfRecurrences,
 ET.LastRecurrence,
 E.EnrollRoleID,
 E.EnrollFee,
 E.EnrollType,
 E.PayPalAccount,
 E.Cancelled,
 E.DetailPage,
 E.DetailNewWin,
 E.DetailURL,
 E.ImageURL,
 E.ImageType,
 E.ImageWidth,
 E.ImageHeight,
 E.ImageDisplay,
 E.Location,
 c.LocationName,
 c.MapURL,
 E.Category,
 b.CategoryName,
 b.Color,
 b.FontColor,
 E.Reminder,
 E.TimezoneOffset,
 E.SendReminder,
 E.ReminderTime,
 E.ReminderTimeMeasurement,
 E.ReminderFrom,
 E.SearchSubmitted,
 E.CustomField1,
 E.CustomField2,
 E.EnrollListView,
 E.DisplayEndDate,
 E.AllDayEvent,
 E.OwnerID,
 OwnerName = O.DisplayName,
 E.LastUpdatedAt,
 LastUpdatedBy = L.DisplayName,
 E.LastUpdatedID,
 (Select ModuleTitle from {databaseOwner}[{objectQualifier}Modules] WHERE ModuleID = E.ModuleID) as ModuleTitle,
 RMOwnerID = r.OwnerID,
 r.RRULE,
 E.OriginalDateBegin,
 E.NewEventEmailSent
 FROM {databaseOwner}[{objectQualifier}Events] E
 inner join {databaseOwner}[{objectQualifier}EventsRecurMaster] AS r on E.RecurMasterID = r.RecurMasterID
 left outer join {databaseOwner}[{objectQualifier}Users] U on E.CreatedByID = U.UserID
 left outer join {databaseOwner}[{objectQualifier}Users] O on E.OwnerID = O.UserID
 left outer join {databaseOwner}[{objectQualifier}Users] L on E.LastUpdatedID = L.UserID
 left join {databaseOwner}[{objectQualifier}EventsCategory] b on E.Category = b.Category
 left join {databaseOwner}[{objectQualifier}EventsLocation] c on E.Location = c.Location
 left join #EventIDs ET on E.EventID = ET.EventID
WHERE E.EventID in (Select EventID from #EventIDs)
   AND (b.CategoryName = @CategoryName or @CategoryName = N'')
ORDER BY E.EventDateBegin, E.EventTimeBegin, E.EventDateEnd
GO