USP_DATALIST_EVENTCONFLICTS

                CREATE procedure dbo.USP_DATALIST_EVENTCONFLICTS
                (
                    @PROGRAMID uniqueidentifier,
                    @SHOWUNCONFLICTED bit = 0
                )
                as 
                    set nocount on;

                    declare @LASTSCHEDULEID uniqueidentifier;

                    select top 1 @LASTSCHEDULEID = SCHEDULEID
                    from (
                        select top 1 SCHEDULEID, DATEADDED from dbo.EVENT order by DATEADDED desc
                        union all
                        select top 1 SCHEDULEID, DATEADDED from dbo.EVENTCONFLICT order by DATEADDED desc
                    ) as SHEDULEIDS
                    order by DATEADDED desc;

                    declare @RETURNTABLE table
                    (
                        ID uniqueidentifier,
                        CONFLICTCOUNT int,
                        LOCATIONCONFLICTCOUNT int,
                        RESOURCECONFLICTCOUNT int,
                        STAFFRESOURCECONFLICTCOUNT int,
                        ALLOWUNDO bit,
                        ADDEDAT datetime,
                        EVENTNAME nvarchar(100),
                        STARTDATE datetime,
                        EVENTTIMESPAN nvarchar(25),
                        LOCATIONS nvarchar(500),
                        RESOURCES nvarchar(500),
                        STAFFRESOURCES nvarchar(500),
                        STARTDATETIME datetime,
                        ENDDATETIME datetime
                    )        

                    insert into @RETURNTABLE
                    (
                        ID,
                        CONFLICTCOUNT,
                        LOCATIONCONFLICTCOUNT,
                        RESOURCECONFLICTCOUNT,
                        STAFFRESOURCECONFLICTCOUNT,
                        ALLOWUNDO,
                        ADDEDAT,
                        EVENTNAME,
                        STARTDATE,
                        EVENTTIMESPAN,
                        LOCATIONS,
                        RESOURCES,
                        STAFFRESOURCES,
                        STARTDATETIME,
                        ENDDATETIME
                    )
                    select
                        EVENTCONFLICT.ID,
                        LOCATIONCONFLICTS.CONFLICTCOUNT + RESOURCECONFLICTS.CONFLICTCOUNT + STAFFRESOURCECONFLICTS.CONFLICTCOUNT,
                        LOCATIONCONFLICTS.CONFLICTCOUNT,
                        RESOURCECONFLICTS.CONFLICTCOUNT,
                        STAFFRESOURCECONFLICTS.CONFLICTCOUNT,
                        case SCHEDULEID
                            when @LASTSCHEDULEID then 1
                            else 0
                        end as ALLOWUNDO,
                        convert(smalldatetime, EVENTCONFLICT.DATEADDED) as ADDEDAT,
                        EVENTCONFLICT.NAME as EVENTNAME,
                        EVENTCONFLICT.STARTDATE,
                        dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENTCONFLICT.STARTTIME) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENTCONFLICT.ENDTIME) as EVENTTIMESPAN,
                        dbo.UFN_EVENTCONFLICT_GETLOCATIONNAME(EVENTCONFLICT.ID) as LOCATIONS,
                        dbo.UFN_EVENTCONFLICT_GETRESOURCESTRING(EVENTCONFLICT.ID) as EVENTRESOURCES,
                        dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCESTRING(EVENTCONFLICT.ID) as EVENTSTAFFRESOURCES,
                        EVENTCONFLICT.STARTDATETIME,
                        EVENTCONFLICT.ENDDATETIME
                    from
                        dbo.EVENTCONFLICT
                    outer apply (
                        select count(distinct LOCATIONCONFLICTS.EVENTID) + count(distinct LOCATIONCONFLICTS.ITINERARYITEMID) as CONFLICTCOUNT
                        from dbo.UFN_EVENTCONFLICT_GETLOCATIONCONFLICTDETAILSBYID(EVENTCONFLICT.STARTDATETIME, EVENTCONFLICT.ENDDATETIME, EVENTCONFLICT.ID, null, null, null, 1, 1) as LOCATIONCONFLICTS
                    ) as LOCATIONCONFLICTS
                    outer apply (
                        select count(distinct RESOURCECONFLICTS.EVENTID) + count(distinct RESOURCECONFLICTS.ITINERARYITEMID) + count(distinct RESOURCECONFLICTS.ITINERARYID) as CONFLICTCOUNT
                        from dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTDETAILS(EVENTCONFLICT.STARTDATETIME, EVENTCONFLICT.ENDDATETIME, dbo.UFN_EVENTCONFLICT_GETRESOURCES_TOITEMLISTXML(ID), null, null, null, 1, 1, 1, null) as RESOURCECONFLICTS
                    ) as RESOURCECONFLICTS
                    outer apply (
                        select count(distinct STAFFRESOURCECONFLICTS.EVENTID) + count(distinct STAFFRESOURCECONFLICTS.ITINERARYITEMID) + count(distinct STAFFRESOURCECONFLICTS.ITINERARYID) as CONFLICTCOUNT
                        from dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTDETAILS(EVENTCONFLICT.STARTDATETIME, EVENTCONFLICT.ENDDATETIME, dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCES_TOITEMLISTXML(ID), null, null, null, 1, 1, 1, null) as STAFFRESOURCECONFLICTS
                    ) as STAFFRESOURCECONFLICTS
                    where
                        PROGRAMID = @PROGRAMID
                        and (@SHOWUNCONFLICTED = 0 or (LOCATIONCONFLICTS.CONFLICTCOUNT + RESOURCECONFLICTS.CONFLICTCOUNT + STAFFRESOURCECONFLICTS.CONFLICTCOUNT) = 0)

                    select RETURNTABLE.ID,
                            RETURNTABLE.CONFLICTCOUNT,
                            RETURNTABLE.ALLOWUNDO,
                            RETURNTABLE.ADDEDAT,
                            RETURNTABLE.EVENTNAME,
                            RETURNTABLE.STARTDATE,
                            RETURNTABLE.EVENTTIMESPAN,
                            coalesce(stuff((case when (RETURNTABLE.LOCATIONCONFLICTCOUNT > 0) then '; Location' else '' end) +
                                            (case when (RETURNTABLE.RESOURCECONFLICTCOUNT > 0) then '; Supply/Equipment resource' else '' end) +
                                            (case when (RETURNTABLE.STAFFRESOURCECONFLICTCOUNT > 0) then '; Staff resource' else '' end), 1, 2, ''), '') as CONFLICTTYPE,
                            RETURNTABLE.LOCATIONS,
                            RETURNTABLE.RESOURCES,
                            RETURNTABLE.STAFFRESOURCES,
                            case
                                when RETURNTABLE.CONFLICTCOUNT > 0 then 'RES:lv_spacer'
                                else 'RES:info'
                            end as WARNINGIMAGE
                    from @RETURNTABLE as RETURNTABLE
                    order by STARTDATETIME