USP_DATALIST_ORGANIZATIONHIERARCHYINVALIDPOSITION

                CREATE procedure dbo.USP_DATALIST_ORGANIZATIONHIERARCHYINVALIDPOSITION 
                (
                    @ORGANIZATIONHIERARCHY xml = null
                )
                as
                    set nocount on;

                    -- build a temporary table containing the values from the XML

                    declare @TempTbl table (
                       [ID] uniqueidentifier,
                       [POSITIONID] uniqueidentifier,
                       [CONSTITUENTID] uniqueidentifier,
                       [DATEFROM] datetime,
                       [DATETO] datetime
                    )

                    declare @idoc int;

                    exec sp_xml_preparedocument @idoc output, @ORGANIZATIONHIERARCHY;

                    insert into @TempTbl 
                    select 
                        ID, 
                        POSITIONID,
                        CONSTITUENTID,
                        DATEFROM,
                        DATETO
                    from openxml(@idoc, '/ORGANIZATIONHIERARCHY/ITEM/POSITIONHOLDERS/ITEM', 2)
                    with(
                        POSITIONID uniqueidentifier '../../ID',
                        ID uniqueidentifier,
                        CONSTITUENTID uniqueidentifier,
                        DATEFROM datetime,
                        DATETO datetime
                    )

                    exec sp_xml_removedocument @idoc;

                    select OPH.ID, OPH.POSITIONID, CONSTITUENT.NAME 
                    from @TempTbl OPH
                    inner join dbo.CONSTITUENT
                        on OPH.CONSTITUENTID = CONSTITUENT.ID
                    where exists(
                            select top 1 
                                SUB.ID
                            from
                                @TempTbl SUB
                            where
                                SUB.CONSTITUENTID = OPH.CONSTITUENTID and
                                SUB.ID <> OPH.ID and
                                (
                                    (SUB.DATETO   between OPH.DATEFROM and OPH.DATETO) or
                                    (OPH.DATETO   between SUB.DATEFROM and SUB.DATETO) or
                                    (SUB.DATEFROM between OPH.DATEFROM and OPH.DATETO) or
                                    (OPH.DATEFROM between SUB.DATEFROM and SUB.DATETO) or

                                    (SUB.DATEFROM is null and OPH.DATEFROM <= SUB.DATETO)   or
                                    (OPH.DATEFROM is null and SUB.DATEFROM <= OPH.DATETO)   or
                                    (SUB.DATETO   is null and OPH.DATETO   >= SUB.DATEFROM) or
                                    (OPH.DATETO   is null and SUB.DATETO   >= OPH.DATEFROM) or

                                    (OPH.DATEFROM is null and SUB.DATEFROM is null) or
                                    (OPH.DATETO   is null and SUB.DATETO   is null) or
                                    (OPH.DATEFROM is null and OPH.DATETO   is null) or
                                    (SUB.DATEFROM is null and SUB.DATETO   is null)
                                )
                        )