Direct Reports

From TekiWiki
Revision as of 08:51, 25 March 2016 by WikiSysop (Talk | contribs) (1 revision imported)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Microsoft SQL Server SQL query to find Direct and Indirect Reports of a manager in PeopleSoft HRMS (that determines the management hierarchy by Position Management):

WITH POSITION_TREE AS ( 
	SELECT PD.POSITION_NBR AS ROOT_POSITION , PD.POSITION_NBR , PD.REPORTS_TO , PD.DESCR , PD.BUSINESS_UNIT , PD.DEPTID , PD.LOCATION , PD.COMPANY , PD.JOBCODE , PD.POSN_STATUS , 0 AS LEVEL 
	FROM PS_POSN_EFF_VW PD 
UNION ALL 
	SELECT POSITION_TREE.ROOT_POSITION AS ROOT_POSITION , PD2.POSITION_NBR , PD2.REPORTS_TO , PD2.DESCR , PD2.BUSINESS_UNIT , PD2.DEPTID , PD2.LOCATION , PD2.COMPANY , PD2.JOBCODE , PD2.POSN_STATUS , LEVEL+1 AS LEVEL 
	FROM PS_POSN_EFF_VW PD2 
	JOIN POSITION_TREE ON PD2.REPORTS_TO = POSITION_TREE.POSITION_NBR 
	WHERE PD2.REPORTS_TO NOT IN (PD2.POSITION_NBR, ' ')
) 
SELECT PD.POSITION_NBR , PT.POSITION_NBR , PT.DESCR , PT.BUSINESS_UNIT , PT.DEPTID , PT.LOCATION , PT.COMPANY , PT.JOBCODE , PT.POSN_STATUS , PT.REPORTS_TO , LEVEL 
FROM PS_POSN_EFF_VW PD , POSITION_TREE PT 
WHERE 
PT.ROOT_POSITION = PD.POSITION_NBR 
AND LEVEL <> 0

This is an example of an ANSI SQL recursive query. Recursive queries have the general format:

WITH view_table AS ( 
	SELECT ... 
UNION ALL 
	SELECT ...
	JOIN view_table ON connect_criteria
)
SELECT ...
FROM view_table
WHERE
start_node_criteria

Microsoft SQL Server