Analytic Function: How to Partition Hierarchical Data [message #679566] |
Mon, 09 March 2020 12:20 |
|
whdyck
Messages: 25 Registered: May 2017
|
Junior Member |
|
|
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
, PC1.fldUnitNoParent
, PC1.fldStartDate
, PC1.fldEndDate
, PC1.fldEndDate_Shdw
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
Cross-posted.
-
Attachment: Results.png
(Size: 4.23KB, Downloaded 1022 times)
|
|
|
Re: Analytic Function: How to Partition Hierarchical Data [message #679568 is a reply to message #679566] |
Mon, 09 March 2020 13:03 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
|
|
|