====== {$page} ======



===== Explanation of SQL =====



This query produces a SQL of the Employee Tree. It can be used as is, but it is often useful as a basis for other Employee based SQL's where the data should be displayed in tree format.



Notes



===== Risk of Data Corruption if Run Improperly =====

**None**. This is a selection query and no data is modified in the running of it.



===== SQL =====



[[code_formatsql]]



DECLARE @ShowInactive bit;



--SET @ShowInActive = ;



SET @ShowInactive = 1;



DECLARE @Spacing int;



SET @Spacing = 4;



SELECT 



  ROW_NUMBER() Over(Order By FormattedPath) Row,


  *



FROM



(



	SELECT 



	  case when Cat5.GroupName IS NOT NULL then SPACE(@Spacing * 4) 



		else case when Cat4.GroupName IS NOT NULL then SPACE(@Spacing * 3)  



		else case when Cat3.GroupName IS NOT NULL then SPACE(@Spacing * 2) 



		else case when Cat2.GroupName IS NOT NULL then SPACE(@Spacing * 1) 



		else case when Cat1.GroupName IS NOT NULL then SPACE(@Spacing * 0) 



		end end end end end +



 		case when IsGroup=1 then '' else SPACE(@Spacing * 1)end +



		Coalesce(Employee.LastName+', '+Employee.FirstName, Cat5.GroupName, Cat4.GroupName, Cat3.GroupName, Cat2.GroupName, Cat1.GroupName) AS FormattedName,



	 



	  Coalesce(Employee.LastName+' '+Employee.FirstName, Cat5.GroupName, Cat4.GroupName, Cat3.GroupName, Cat2.GroupName, Cat1.GroupName) AS NodeName,



	  Coalesce(Employee.ID, Cat5.ID, Cat4.ID, Cat3.ID, Cat2.ID, Cat1.ID) AS NodeID,



	  Coalesce(Employee.ClasstypeID, Cat5.ClasstypeID, Cat4.ClasstypeID, Cat3.ClassTypeID, Cat2.ClasstypeID, Cat1.ClasstypeID) AS NodeClassTypeID,  



	  Coalesce(Employee.IsActive, Cat5.IsActive, Cat4.IsActive, Cat3.IsActive, Cat2.IsActive, Cat1.IsActive) AS IsActive,



	  IsGroup,



	  coalesce(Cat5.ID - Cat5.ID + 5, Cat4.ID - Cat4.ID + 4, Cat3.ID - Cat3.ID + 2, Cat2.ID - Cat2.ID + 2, Cat1.ID - Cat1.ID + 1, 0) AS Depth,



	  Coalesce(Cat5.ID, Cat4.ID, Cat3.ID, Cat2.ID, Cat1.ID) AS ParentID,



	  Coalesce(Cat5.ClassTypeID, Cat4.ClassTypeID, Cat3.ClassTypeID, Cat2.ClassTypeID, Cat1.ClassTypeID) AS ParentClassTypeID,



	 



	  coalesce(Employee.LastName+', '+Employee.FirstName, '') AS EmployeeName,



	  coalesce(Cat1.GroupName, '') AS Category1Name,



	  coalesce(Cat2.GroupName, '') AS Category2Name,



	  coalesce(Cat3.GroupName, '') AS Category3Name,



	  coalesce(Cat4.GroupName, '') AS Category4Name,



	  coalesce(Cat5.GroupName, '') AS Category5Name,



	 



	  Employee.ID as EmployeeID,



	  Cat1.ID as Category1ID,



	  Cat2.ID as Category2ID,



	  Cat3.ID as Category3ID,



	  Cat4.ID as Category4ID,



	  Cat5.ID as Category5ID,



	 



	  coalesce('1-'+Cat1.GroupName + '\', '') +



		coalesce('2-'+Cat2.GroupName + '\', '') + 



		coalesce('3-'+Cat3.GroupName + '\', '') +



		coalesce('4-'+Cat4.GroupName + '\', '') +



		coalesce('5-'+Cat5.GroupName + '\', '') +



		coalesce(Employee.LastName+', '+Employee.FirstName, '') as FormattedPath



	 



	from EmployeeGroup as Cat1



	JOIN (SELECT 1 AS IsLevel1 UNION SELECT 0) Level1 ON Cat1.ID > 0 and Cat1.ParentID is NULL -- create two rows for each of the above



	left join EmployeeGroup as Cat2 on Cat2.ParentID = Cat1.ID and IsLevel1 = 0 



	JOIN (SELECT 1 AS IsLevel2 UNION SELECT 0) Level2 ON 1=1 -- create two rows for each of the above



	



	left join EmployeeGroup as Cat3 on Cat3.ParentID = Cat2.ID and IsLevel1 = 0 and IsLevel2 = 0 



	JOIN (SELECT 1 AS IsLevel3 UNION SELECT 0) Level3 ON 1=1 -- create two rows for each of the above



	



	left join EmployeeGroup as Cat4 on Cat4.ParentID = Cat3.ID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 



	JOIN (SELECT 1 AS IsLevel4 UNION SELECT 0) Level4 ON 1=1 -- create two rows for each of the above



	



	left join EmployeeGroup as Cat5 on Cat5.ParentID = Cat4.ID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 and IsLevel4 = 0



	JOIN (SELECT 1 AS IsLevel5 UNION SELECT 0) Level5 ON 1=1 -- create two rows for each of the above



	



    JOIN (SELECT 1 AS IsGroup UNION SELECT 0) Temp ON 1=1 -- create two rows for each of the above



	left join Employee on Employee.GroupID = coalesce(Cat5.ID, Cat4.ID, Cat3.ID, Cat2.ID, Cat1.ID) and IsGroup = 0 and coalesce(Employee.ID,1) > 0



	                            



	where (IsLevel1 + IsLevel2 + IsLevel3 + IsLevel4 + IsLevel5) = 1



	  and (   (IsLevel1 = 1)



	       or (IsLevel2 = 1 and Cat2.ID IS NOT NULL)



	       or (IsLevel3 = 1 and Cat3.ID IS NOT NULL)



	       or (IsLevel4 = 1 and Cat4.ID IS NOT NULL)



	       or (IsLevel5 = 1 and Cat5.ID IS NOT NULL) )



	



) EmployeeTree



WHERE ((IsActive = 1) or (@ShowInactive = 1))



order by Row



[[code]]



===== Version Information =====
  * Entered : 1/8/2010
  * Version : Control 4.0+



===== Related SQLs =====
  * Backlinks [[include_pagepage_componentbacklinks]]