USP_DATALIST_PROGRAMCALENDAR_PROGRAMEVENT

Provides a calendar view of all scheduled events.

Parameters

Parameter	Parameter Type	Mode	Description
@PROGRAMID	uniqueidentifier	IN	Program
@EVENTLOCATIONID	uniqueidentifier	IN	Location
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date

Definition

Copy



CREATE procedure dbo.USP_DATALIST_PROGRAMCALENDAR_PROGRAMEVENT
(
        @PROGRAMID uniqueidentifier = null,
        @EVENTLOCATIONID uniqueidentifier = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
)
as
    set nocount on;

    select
                        EVENT.ID,
                        EVENT.NAME,
                        EVENT.DESCRIPTION,
                        EVENT.STARTDATE,
                        EVENT.STARTTIME,
                        EVENT.ENDDATE,
                        EVENT.ENDTIME
                    from
                        dbo.EVENT
                        inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
                    where
                        (@PROGRAMID is null or @PROGRAMID = PROGRAM.ID) and
                        (@EVENTLOCATIONID is null or @EVENTLOCATIONID in (select EVENTLOCATIONID from PROGRAMEVENTLOCATION where EVENTID = EVENT.ID)) and
                        PROGRAM.ISACTIVE = 1 and
            (
                            (@STARTDATE is null and @ENDDATE is null) or
                            (
                                (
                                    (EVENT.STARTDATE <= @ENDDATE) and (@ENDDATE is not null)
                                ) 
                                and
                                (
                                    (EVENT.ENDDATE >= @STARTDATE) and (@STARTDATE is not null)
                                )
                            )
                        )
                    order by
                        STARTDATE,
                        STARTTIME,
                        NAME;

                    return 0;