ALTER view [dbo].[vAssetUserPerson] as
select
	t.NodeId,
	t.[User],
	t.PersonId,
	PersonManagerId
from (
	select
		u.intNodeId NodeId,
		pk.txtValue [User],
		u.iPersonId PersonId,
		u.liPersonManagerPersonId PersonManagerId,
		row_number() over (partition by u.intNodeId order by u.u) rn
	from (
		select
			n.intNodeId,
			p.iPersonId,
			p.liPersonManagerPersonId,
			1 u
		from tblNode n
			join tPerson p on lower(p.sPersonAdGuid)=lower(n.txtLDAPGUID) and p.dPersonRemoved is null
		where n.lintClassId=7 and ISNULL(n.txtLDAPGUID, '') != '' and ISNULL(p.sPersonAdGuid, '') != ''

		union all

		select
			n.intNodeId,
			pe.iPersonId,
			pe.liPersonManagerPersonId,
			2 prio
		from tblNode n
			join tblProperty p on p.lintNodeId=n.intNodeId and p.ParentNodeId is null
			join tblKind k on k.intKindId=p.lintKindId and k.intKindCode=74
			join tPerson pe on lower(pe.sPersonPersonalNumber)=lower(p.txtValue) and pe.dPersonRemoved is null
		where n.lintClassId=7 and ISNULL(p.txtValue, '') != '' and ISNULL(pe.sPersonPersonalNumber, '') != ''

		union all
		
		select
			n.intNodeId,
			pe.iPersonId,
			pe.liPersonManagerPersonId,
			3 prio
		from tblNode n
			join tblProperty p on p.lintNodeId=n.intNodeId and p.ParentNodeId is null
			join tblKind k on k.intKindId=p.lintKindId and k.intKindCode=54
			join tPerson pe on lower(p.txtValue) = lower(pe.sPersonLogin) and pe.dPersonRemoved is null
		where n.lintClassId=7 and ISNULL(p.txtValue, '') != '' and ISNULL(pe.sPersonLogin, '') != ''

		union all

		select
			n.intNodeId,
			pe.iPersonId,
			pe.liPersonManagerPersonId,
			4 prio
		from tblNode n
			join tblProperty p on p.lintNodeId=n.intNodeId and p.ParentNodeId is null
			join tblKind k on k.intKindId=p.lintKindId and k.intKindCode=54
			join tPerson pe on lower(p.txtValue) = lower(pe.SamAccountName) and pe.dPersonRemoved is null
		where n.lintClassId=7 and ISNULL(p.txtValue, '') != '' and ISNULL(pe.SamAccountName, '') != ''

		union all

		select
			n.intNodeId,
			pe.iPersonId,
			pe.liPersonManagerPersonId,
			5 prio
		from tblNode n
			join tblProperty p on p.lintNodeId=n.intNodeId and p.ParentNodeId is null
			join tblKind k on k.intKindId=p.lintKindId and k.intKindCode=46
			join tPerson pe on lower(pe.sPersonEmail)=lower(p.txtValue) and pe.dPersonRemoved is null
		where n.lintClassId=7 and ISNULL(pe.sPersonEmail, '') != '' and ISNULL(p.txtValue, '') != ''
		) u
		join vPropertyKind pk on pk.lintNodeId=u.intNodeId and pk.intKindCode=5
	) t
where rn=1