dbo.QER_FTPersonHeadCirc_calc

1create function dbo.QER_FTPersonHeadCirc_calc(@soFar dbo.QER_YPersonHeadCircular readonly ) returns @erg table ( UID_Person varchar(38) collate database_default
2 Not Null , UID_PersonHead varchar(38) collate database_default Not Null   , IsDirectReportsContained bit default 0 , IsDirectReportsOnly bit default 0
3  , IsHeadOrgContained bit default 0 , IsDelegationContained bit default 0  ) with schemabinding as begin declare @work dbo.QER_YPersonHeadCircular declare
4 @lauf int insert into @work( UID_Person , UID_PersonHead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular , IsDelegationContained , IsDirectReportsOnly
5  ) select UID_Person , UID_PersonHead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular , IsDelegationContained , IsDirectReportsOnly from
6 @soFar select @lauf = 1 while @lauf > 0 begin insert into @work(uid_person, uid_personhead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular
7 , IsDelegationContained , IsDirectReportsOnly  ) select l.uid_person, h.uid_personhead , max(convert(int, l.IsDirectReportsContained | h.IsDirectReportsContained
8)) as IsDirectReportsContained , max(convert(int, l.IsHeadOrgContained | h.IsHeadOrgContained)) as IsHeadOrgContained      , 0 as IsCircular , max(convert
9(int, l.IsDelegationContained | h.IsDelegationContained )) as IsDelegationContained , Min(convert(int, l.IsDirectReportsOnly & h.IsDirectReportsOnly)) 
10as IsDirectReportsOnly  from @work l join @work h on l.UID_PersonHead = h.UID_Person group by l.UID_Person, h.UID_PersonHead having not exists (select 
11top 1 1 from @work e where e.UID_Person = l.UID_Person and e.UID_PersonHead = h.uid_personhead ) and l.UID_Person <> h.UID_PersonHead select @lauf = @@ROWCOUNT
12 end update @work set IsCircular = 1 , IsDelegationContained = l.IsDelegationContained | h.IsDelegationContained , IsDirectReportsContained = l.IsDirectReportsContained
13 | h.IsDirectReportsContained , IsDirectReportsOnly = l.IsDirectReportsOnly & h.IsDirectReportsOnly , IsHeadOrgContained = l.IsHeadOrgContained | h.IsHeadOrgContained
14 from @work l join (select e.UID_PersonHead, e.UID_Person , e.IsDelegationContained, e.IsDirectReportsContained, e.IsDirectReportsOnly, e.IsHeadOrgContained
15 from @work e ) as h on l.uid_person = h.uid_personhead and l.uid_personhead = h.uid_person insert into @erg(UID_Person, UID_PersonHead, IsDirectReportsContained
16, IsDirectReportsOnly , IsDelegationContained, IsHeadOrgContained  ) select UID_Person, UID_PersonHead, IsDirectReportsContained, IsDirectReportsOnly ,
17 IsDelegationContained, IsHeadOrgContained  from @work w where w.IsCircular = 1    return end 
18