Direct Reports
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Direct Reports
| Line 1: | Line 1: | ||
| - | Direct Reports | + | [[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): |
<pre> | <pre> | ||
WITH POSITION_TREE AS ( | 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 | 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 | + | FROM PS_POSN_EFF_VW PD |
UNION ALL | 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 | 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 | + | FROM PS_POSN_EFF_VW PD2 |
JOIN POSITION_TREE ON PD2.REPORTS_TO = POSITION_TREE.POSITION_NBR | JOIN POSITION_TREE ON PD2.REPORTS_TO = POSITION_TREE.POSITION_NBR | ||
WHERE PD2.REPORTS_TO NOT IN (PD2.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 | 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 | + | FROM PS_POSN_EFF_VW PD , POSITION_TREE PT |
WHERE | WHERE | ||
PT.ROOT_POSITION = PD.POSITION_NBR | PT.ROOT_POSITION = PD.POSITION_NBR | ||
AND LEVEL <> 0 | AND LEVEL <> 0 | ||
</pre> | </pre> | ||
| + | |||
| + | This is an example of an ANSI SQL recursive query. Recursive queries have the general format: | ||
| + | |||
| + | <pre> | ||
| + | WITH view_table AS ( | ||
| + | SELECT ... | ||
| + | UNION ALL | ||
| + | SELECT ... | ||
| + | JOIN view_table ON connect_criteria | ||
| + | ) | ||
| + | SELECT ... | ||
| + | FROM view_table | ||
| + | WHERE | ||
| + | start_node_criteria | ||
| + | </pre> | ||
| + | |||
| + | [[Microsoft SQL Server]] | ||
