Kategorie: SQL Server 2017

/*
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)