|Analytic Function: How to Partition Hierarchical Data [message #679566]
||Mon, 09 March 2020 12:20
Registered: May 2017
I have the following result set from a SQL statement:|
This captures two hierarchy chains of employee-manager relationships for Emp=10558, each chain based on a different point in time. So perhaps the employee 10588 worked in two different departments, so had two manager hierarchies over this time, as follows:
1. 10558 --> 00222 --> 0508 --> 00503 --> 00499
2. 10558 --> 0507
I'd like to be able to get the Max StartDate and Min EndDate for this data, but only within each chain.
So, for the above data, ...
1. The first chain (10558 >> 00499) should get
Max StartDate: 17-Jan-2020
Min EndDate: 31-Dec-9998
2. The 2nd chain (10558 >> 0507) should get
Max StartDate: 01-Jan-2019
Min EndDate: 05-Feb-2019
What I'm really trying to identify (with SQL) is when Emp 10558's highest-level manager was 00499 and when his highest-level manager was 0507.
I was thinking to use analytic functions MAX and MIN, but not sure what partition I would use to get these values. If I just calculate them "OVER ()", I'll get
Max StartDate = 17-Jan-2020
Min EndDate = 05-Feb-2019
but that's not what I want. I want two non-overlapping periods during which Emp's highest-level manager was 00499 vs. 0507.
Not sure you need to see my code used to generate the above data, but if so, here it is:
SELECT PCActv.fldUnitNoChild AS Emp
, PCActv.fldUnitNoParent AS Mgr
, PCActv.fldStartDate AS StartDate
, PCActv.fldEndDate_Shdw AS EndDate
, LEVEL AS Lvl
FROM (SELECT PC1.fldUnitNoChild
FROM usrUntMst.tblUntMstParentChild PC1
WHERE PC1.fldVoid = 0) PCActv
-- AND NVL('&&TestDate', SYSDATE) BETWEEN fldStartDate AND fldEndDate_Shdw)
CONNECT BY PRIOR PCActv.fldUnitNoParent = PCActv.fldUnitNoChild
AND PRIOR PCActv.fldStartDate < PCActv.fldEndDate_Shdw
AND PRIOR PCActv.fldEndDate_Shdw > PCActv.fldStartDate
START WITH PCActv.fldUnitNoChild = '&&UnitNo'
ORDER BY LEVEL
(Size: 4.23KB, Downloaded 71 times)