Roger you sir are a saint.
I did the module update and can see the new stored procedure. Much easier to work with for a layman like myself.
I imagine I just have a simple syntax error now. The script executes but does not have any noticeable effect, meaning my conditional is doing nothing. If I replace all the references manually in the SQL of @EndDate and @BeginDate with '2012-12-13' and '2012-01-01' then I get the desired result.
Again thanks a million for all your help with this.
Here is the current SQL the part I added in BOLD:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/**** EventsGetByRange ****/
ALTER PROCEDURE [dbo].[EventsGetByRange]
(
@ModuleIDs nvarchar(255),
@BeginDate datetime,
@EndDate datetime,
@CategoryName nVarchar(50)
)
AS
SET DATEFORMAT mdy
IF @ModuleIDs = N'618'
Select @BeginDate = '2012-01-01'
Select @EndDate = '2012-12-31'
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 dbo.[Events] E
LEFT OUTER JOIN dbo.[EventsMaster] M ON E.ModuleID = M.SubEventID
LEFT JOIN dbo.[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 BETWEEN @BeginDate AND DATEADD(DAY,1, @EndDate)))
AND E.Approved = 1
AND E.Cancelled = 0
AND (E.ModuleID in (SELECT * FROM dbo.[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 dbo.[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 dbo.[Modules] WHERE ModuleID = E.ModuleID) as ModuleTitle,
RMOwnerID = r.OwnerID,
r.RRULE,
E.OriginalDateBegin,
E.NewEventEmailSent
FROM dbo.[Events] E
inner join dbo.[EventsRecurMaster] AS r on E.RecurMasterID = r.RecurMasterID
left outer join dbo.[Users] U on E.CreatedByID = U.UserID
left outer join dbo.[Users] O on E.OwnerID = O.UserID
left outer join dbo.[Users] L on E.LastUpdatedID = L.UserID
left join dbo.[EventsCategory] b on E.Category = b.Category
left join dbo.[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