ALTER VIEW [dbo].[vUserLicence] AS
SELECT PersonId, LicenceTypeId, mString AS LicenceType FROM (
SELECT iPersonId AS PersonId, MAX(LicenceType) AS LicenceTypeId FROM (
	select p.iPersonId,1 as LicenceType from (
		select HdSectionId, PersonId from HdSectionSolver
		union all
		select HdSectionId, PersonId from HdSectionManager
		union all
		select HdSectionId, PersonId from vHdSectionOperator
		union all
		select HdSectionId, PersonId from HdSectionReader
		union all 
		select liSlaAccessHdSectionId,liSlaAccessPersonId from vSlaAccess 
		union all 
		select SectionId, PersonId from vHdSectionExceptionalSolver 
	) rol
	join tPerson p on p.iPersonId=rol.PersonId
	join tHdSection s on s.iHdSectionId=rol.HdSectionId
where p.bHidden=0
	and p.dPersonRemoved is null
	and p.bPersonGuest=0
    and p.bPersonAccountDisabled=0
    and p.PersonBehaviorId = 1
	and s.dHdSectionRemoved is null
	and iHdSectionId NOT IN (
		SELECT SA2.liSlaAccessHdSectionId FROM vSlaAccess SA2
		LEFT JOIN tPerson P2 ON SA2.liSlaAccessPersonId = P2.iPersonId
		LEFT JOIN tHdSection S2 ON S2.iHdSectionId = SA2.liSlaAccessHdSectionId
		WHERE P2.bPersonGuest = 1 AND S2.dHdSectionRemoved is null
	)
	and s.HomePage = 0
    and (liHdSectionParentHdSectionId IS NULL 
		OR liHdSectionParentHdSectionId NOT IN (SELECT iHdSectionId FROM tHdSection WHERE HomePage = 1)
    )
UNION

	select  p.iPersonId, 2 as LicenceType from 
						(
							select HdSectionId,PersonId from HdSectionSolver
							union all
							select HdSectionId, PersonId from HdSectionManager
							union all
							select HdSectionId, PersonId from vHdSectionOperator
							union all
							select SectionId, PersonId from vHdSectionExceptionalSolver 
						) rol 
						join tPerson p on p.iPersonId=rol.PersonId 
						join tHdSection s on s.iHdSectionId=rol.HdSectionId 
					where p.bHidden=0 
						and p.dPersonRemoved is null 
						and p.bPersonGuest=0 
						and p.bPersonAccountDisabled=0 
						and p.PersonBehaviorId = 1 
						and s.dHdSectionRemoved is null 
						and iHdSectionId IN (
							SELECT SA2.liSlaAccessHdSectionId FROM vSlaAccess SA2
	                        LEFT JOIN tPerson P2 ON SA2.liSlaAccessPersonId = P2.iPersonId
	                        LEFT JOIN tHdSection S2 ON S2.iHdSectionId = SA2.liSlaAccessHdSectionId
	                        WHERE P2.bPersonGuest = 1 AND S2.dHdSectionRemoved is null
	                        UNION SELECT iHdSectionId FROM tHdSection WHERE ForceUseSolverLicense = 1
						)
						and s.HomePage = 0
                        and (liHdSectionParentHdSectionId IS NULL 
							OR liHdSectionParentHdSectionId NOT IN (SELECT iHdSectionId FROM tHdSection WHERE HomePage = 1)
                        )
                    group by p.iPersonId
UNION 
select  p.iPersonId, 0 as LicenceType from tPerson p
) A GROUP BY iPersonId
) B
LEFT JOIN tString TS ON B.LicenceTypeId != 0 AND B.LicenceTypeId + 68 = TS.iStringId