USP_DATALIST_EVENTBYWEEK

Get all events in a given week
Parameters

Parameter	Parameter Type	Mode	Description
@EVENTWITHDATE	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@SORTBYPROGRAM	bit	IN	Sort by program
Definition

Copy


                    CREATE procedure dbo.USP_DATALIST_EVENTBYWEEK
                    (
                        @EVENTWITHDATE uniqueidentifier,
                        @SORTBYPROGRAM bit = 0
                    )
                    as
                        set nocount on;

                        declare @DATETOCOPY datetime;
                        select @DATETOCOPY = STARTDATE from dbo.EVENT where ID = @EVENTWITHDATE;

                        select
                            EVENT.ID,
                            EVENT.PROGRAMID,
                            dbo.UFN_TRANSLATIONFUNCTION_PROGRAM_GETNAME(EVENT.PROGRAMID) as PROGRAM,
                            EVENT.NAME,
                            EVENT.STARTDATE as EVENTDATE,
                            EVENT.STARTTIME as STARTTIMEHM,
                            EVENT.ENDTIME as ENDTIMEHM
                        from
                            dbo.EVENT
                        inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
                        where
                            EVENT.STARTDATE between dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATETOCOPY, 0) and dbo.UFN_DATE_THISWEEK_LASTDAY(@DATETOCOPY, 1)
                            and PROGRAM.ISACTIVE = 1
                        order by
                            case @SORTBYPROGRAM
                                when 1 then EVENT.PROGRAMID
                            end,
                            EVENTDATE,
                            STARTTIMEHM;

                        return 0;