Sorry for the late answer.

If you're still struggling with the problem try this. As there may be multiple intervals for a given MapPerson/Mapgroup, we need to know next interval info. SQL 2012 has LEAD function for this. Not sure if any gaps between sequetnial intervals may exist . So the query may need some tweaking to take gaps into account.

with others as (

select top(1) mp2.MapDepartment , mp2.MapValidFrom, mp2.MapValidUpto

from [dbo].[MstrMap] mp2

where mp2.MapPerson =N'' and mp2.MapGroup = N''

), t1 as (

select

ruletype = case when (p.PrsnCode = mp.MapPerson) then 'matchig by person code'

when mp.MapDepartment is null then 'no matches found'

else 'matching by group' end

, p.PrsnCode, p.PrsnName, p.PrsnGrpCode

, mp.MapDepartment, mp.MapValidFrom, mp.MapValidUpto

, nxtFrom = lead(mp.MapValidFrom,1,NULL) over (partition by PrsnCode order by mp.MapValidFrom)

from [dbo].[MstrPerson] p

left join [dbo].[MstrMap] mp on p.PrsnCode = mp.MapPerson

or (mp.MapPerson = N'' and p.PrsnGrpCode = mp.MapGroup)

)

select t1.ruletype, t1.PrsnCode, t1.PrsnName, t1.PrsnGrpCode

, intervals.MapDepartment, intervals.MapValidFrom, intervals.MapValidUpto

from t1

outer apply (

select t1.MapDepartment, t1.MapValidFrom, t1.MapValidUpto

where t1.MapDepartment is not null

union all

-- Should it take gaps (nxtFrom - MapValidUpto) > 1 into account ?

select others.MapDepartment, t1.MapValidUpto, others.MapValidUpto

from others

where t1.MapValidUpto is not null and t1.nxtFrom is null

union all

select others.MapDepartment, others.MapValidFrom, others.MapValidUpto

from others

where t1.MapDepartment is null) intervals