USP_TRACK_CALENDARITEMS

create procedure dbo.USP_TRACK_CALENDARITEMS
(
    @TRACKID uniqueidentifier,
    @TYPECODE tinyint
)
as
begin
    set nocount on;

    -- Hack for making the calendar appear as if it doesn't have specific dates

    declare @STARTDATE datetime;
    set @STARTDATE = '1984-01-01';

    select
        ID,
        NAME,
        case when LOCATION is not null and len(LOCATION) > 0
            then LOCATION + char(10)
            else ''
        end +
        case when RESOURCES is not null and len(RESOURCES) > 0
            then RESOURCES + char(10)
            else ''
        end +
        case when STAFFRESOURCES is not null and len(STAFFRESOURCES) > 0
            then STAFFRESOURCES
            else ''
        end as DESCRIPTION,
        STARTTIME,
        ENDTIME,
        STARTDATE,
        ENDDATE
    from
        (select
            ID,
            NAME,
            case
                when EVENTLOCATIONID is not null then
                    dbo.UFN_EVENTLOCATION_GETNAME(EVENTLOCATIONID)
                else
                    dbo.UFN_PROGRAM_GETLOCATIONNAME(PROGRAMID)
            end [LOCATION],
            dbo.UFN_TRACKITEMRESOURCE_GETRESOURCESTRING(TRACKITEM.ID) as RESOURCES,
            dbo.UFN_TRACKITEMSTAFFRESOURCE_GETSTAFFRESOURCESTRING(TRACKITEM.ID) as STAFFRESOURCES,
            STARTTIME,
            ENDTIME,
            dateadd(day, STARTDAY, @STARTDATE) STARTDATE,
            dateadd(day, ENDDAY, @STARTDATE) ENDDATE
        from
            dbo.TRACKITEM
        where
            TRACKID = @TRACKID and
            TYPECODE = @TYPECODE) T
    order by
        STARTDATE,
        STARTTIME;
end