/*
Hierarchie von Firmen
---------------------
Egal wo man steht, erst den obersten ParentAccount suchen
und von dort aus alle Kinder feststellen
--http://www.bidn.com/blogs/KeithHyer/bidn-blog/2293/hierarchy-with-recursive-cte-and-a-little-borrowed-sorting-trickery
*/
/*Muster MSCRM BusinessUnit:*/
--Select Name, * from Team order by 1
With hBU(Name, BusinessUnitId, ParentBusinessUnitIdName, ParentBusinessUnitId, Levl, sortBU)
As (
/*Anker*/
Select Name, BusinessUnitId, ParentBusinessUnitIdName, ParentBusinessUnitId , 0 Levl , CONVERT( VARchar(MAX) , ( BusinessUnit.Name ) ) as sortBU
From BusinessUnit
Where ParentBusinessUnitId IS NULL /*and Name = 'dkacrm'*/
union all
/*Recursion*/
Select BusinessUnit.Name, BusinessUnit.BusinessUnitId, BusinessUnit.ParentBusinessUnitIdName, BusinessUnit.ParentBusinessUnitId , Levl + 1 as Levl, CONVERT( VARchar(MAX) , ( SortBU + BusinessUnit.Name ) ) as sortBU
From BusinessUnit
inner join hBU on hBU.BusinessUnitId = BusinessUnit.ParentBusinessUnitId
Where BusinessUnit.ParentBusinessUnitId IS NOT NULL
)
Select coalesce(replicate(char(124)+' ',Levl-1)+replicate(char(43)+' ',Levl-Levl+1) ,'') + hBU.Name , *
from hBU
order by sortBU
/*Muster MSCRM Account:
Wobei die Felder CustomerTypeCode und ImportSequenceNumber nur Firmen/Kunden-spezifisch sind
*/
With hAcc(Name, Accountid, ParentAccountIdName, ParentAccountid, CustomerTypeCode, ImportSequenceNumber, Levl, SortAcc)
As (
/*Anker*/
Select Name, AccountId, ParentAccountIdName, ParentAccountId, CustomerTypeCode, ImportSequenceNumber, 0 Levl, CONVERT( Varchar(MAX) , Name ) as SortAcc
From Account
Where 1=1
and CustomerTypeCode in (200002 /*Debitoren*/, 200000 /*Filialen*/)
and ParentAccountId IS NULL
union all
/*Recursion in CTE*/
Select Account.Name, Account.AccountId, Account.ParentAccountIdName, Account.ParentAccountId, Account.CustomerTypeCode, Account.ImportSequenceNumber, Levl + 1, CONVERT( Varchar(MAX) , SortAcc + Account.Name ) as SortAcc
From Account
inner join hAcc on hAcc.Accountid=Account.ParentAccountId
Where 1=1
and Account.CustomerTypeCode in (200002 /*Debitoren*/, 200000 /*Filialen*/)
and Account.ParentAccountId IS NOT NULL
)
Select coalesce(replicate(char(124)+' ',Levl-1)+replicate(char(43)+' ',Levl-Levl+1) ,'') + Name as Name,
CustomerTypeCode, StringMap.Value as CustomerTypeCodeReadable,
/*Case when CustomerTypeCode = 200000 then 'Filiale' when CustomerTypeCode = 200002 Then 'Debitor' else '???' end CustomerTypeCodeReadable,*/
AccountId, ParentAccountId, ParentAccountIdName, CustomerTypeCode, ImportSequenceNumber, Levl
From hAcc
left outer join StringMap on StringMap.AttributeName = 'CustomerTypeCode' and StringMap.LangId = 1031 and StringMap.AttributeValue = hAcc.CustomerTypeCode
Order by SortAcc
/*Muster SLX Account:*/
Declare @Ankerid char(12);
SET @Ankerid = 'A6UNMA000DQO'; --z.B. Firma Muster GmbH & Co. KG (ist ein Kind weil es ein Parent hat)
--SET @Ankerid = 'A6UJ9A00050W'; --z.B. Muster Holding AG (ist OberAccount)
--Von hier aus den Obervater finden:
DECLARE @ToplevelAccountid char(12);
WITH hAcc(Account, Accountid, ParentAccountid, Levl)
AS (
--Anker:
Select Account, Accountid, Parentid AccountParentid, 0 Levl
from Account
where Accountid=@Ankerid
union all
--Rekursion in CTE:
Select Account.Account Account, Account.Accountid Accountid, Account.Parentid ParentAccountid, Levl + 1
from Account
inner join hAcc on hAcc.ParentAccountid = Account.Accountid
--where Parentid IS NOT NULL
)
--Select Account, Accountid, ParentAccountid, Levl from hAcc where ParentAccountid IS NULL
Select @ToplevelAccountid = Accountid from hAcc where ParentAccountid IS NULL;
--Print @ToplevelAccountid
--Von hier aus alle Kinder:
WITH hAcc(Account, Accountid, ParentAccount, ParentAccountid, Levl, SortAcc)
AS (
--Anker (also alle Sätze, die keine Kinder mehr haben):
Select Account, Accountid, cast(NULL AS Varchar(128)) ParentAccount, Parentid ParentAccountid, 0 Levl
,CONVERT( VARchar(MAX) , Account ) as SortAcc
from Account
where Parentid IS NULL
and Accountid=@ToplevelAccountid
union all
--Rekursion in CTE:
Select Account.Account Account, Account.Accountid Accountid, hAcc.Account ParentAccount, Account.Parentid ParentAccountid, Levl + 1
, CONVERT( VARchar(MAX) , ( SortAcc + Account.Account ) ) as SortAcc
from Account
inner join hAcc on hAcc.Accountid=Account.Parentid
where Parentid IS NOT NULL
)
Select
Account Account,
coalesce(replicate(char(124)+' ',Levl-1)+replicate(char(43)+' ',Levl-Levl+1) ,'') Leveling,
'' FirstChar,
Accountid, ParentAccount, ParentAccountid, Levl,
(Case when @Ankerid = Accountid Then 1 else 0 end) Markierung
,SortAcc
--,Row_Number() Over(order by SortAcc asc) RowNum
from hAcc
order by SortAcc
/*
--Beispiel:
Select parentid, Account, Accountid from Account where Accountid='A6UJ9A000F01'
Select parentid, Account, Accountid from Account where Accountid='A6UJ9A00050W'
*/
Select * from account
Select * from gc_account_ext
Select * from ACC_CORPORATE
Select a.ACCOUNT, a.ACCOUNTID, ac.ACCOUNTID, ac.ACC_PARENTID
from account a
inner join ACC_CORPORATE ac on a.ACCOUNTID=ac.ACCOUNTID
;WITH LoopIT( PARENT, CHILD )
AS (
--Anker (also alle Sätze, die keine Kinder mehr haben):
Select ac.ACC_PARENTID, ac.ACCOUNTID
From ACC_CORPORATE ac
where ac.ACC_PARENTID NOT IN (Select ac2.ACCOUNTID From ACC_CORPORATE ac2)
union all
--Rekursion in CTE:
Select r.ACC_PARENTID, r.ACCOUNTID
From LoopIT r
inner join ACC_CORPORATE acr on r.ACCOUNTID = acr.ACC_PARENTID
)
Select PARENT, CHILD FROM LoopIT
Hinweis: Standardmäßig sind jedoch nur 100 Rekursionen erlaubt und werden mit einem Fehler abgerochen. Lösung: Ganz an das Ende des T-SQL Code, also auch hinter „WHERE“-Bedingung/en und „Order BY“:
OPTION (MAXRECURSION 32767)
