USP_DATALIST_EVENTRESOURCEBYRESOURCE

Presents a list of the events for which a resource is in use.
Parameters

Parameter	Parameter Type	Mode	Description
@RESOURCEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@PROGRAMID	uniqueidentifier	IN	Program
@DATERANGE	tinyint	IN	Date range
Definition

Copy


        create procedure dbo.USP_DATALIST_EVENTRESOURCEBYRESOURCE
        (
          @RESOURCEID uniqueidentifier,
          @PROGRAMID uniqueidentifier = null,
          @DATERANGE tinyint = 5
        )
        as
            set nocount on;

                declare @TODAY datetime = GetDate();

                declare @STARTDATE datetime = null;
                declare @ENDDATE datetime = null;

                if @DATERANGE = 0
                    begin
                        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@TODAY);
                        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@TODAY);
                    end
                else if @DATERANGE = 1
                    begin
                        set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@TODAY, 0);
                        set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@TODAY, 1);
                    end
                else if @DATERANGE = 2
                    begin
                        set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@TODAY, 0);
                        set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@TODAY, 1);
                    end
                else if @DATERANGE = 3
                    begin
                        set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@TODAY, 0);
                        set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@TODAY, 1);
                    end
                else if @DATERANGE = 4
                    begin
                        set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@TODAY, 0);
                        set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@TODAY, 1);
                    end

          select EVENTRESOURCE.ID,
                 EVENT.ID as EVENTID,
                 EVENT.NAME,
                 dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATIONNAME,
                 EVENT.CAPACITY,
                 EVENT.STARTDATE,
                 EVENT.STARTTIME,
                 EVENT.ENDDATE,
                 EVENT.ENDTIME
          from dbo.EVENTRESOURCE
            inner join dbo.EVENT
              on EVENTRESOURCE.EVENTID = EVENT.ID
          where EVENTRESOURCE.RESOURCEID = @RESOURCEID and
                ((@PROGRAMID is null) or (@PROGRAMID = EVENT.PROGRAMID)) and
                ((@STARTDATE is null) or 
                 (EVENT.STARTDATE between @STARTDATE and @ENDDATE) or
                 (EVENT.ENDDATE between @STARTDATE and @ENDDATE) or
                 ((EVENT.STARTDATE >= @STARTDATE) and (EVENT.ENDDATE <= @ENDDATE)))