{$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 1)

order by Row

code

Version Information

  • Entered : 1/8/2010
  • Version : Control 4.0+

Related SQLs

1)
IsActive = 1) or (@ShowInactive = 1
You could leave a comment if you were logged in.