Home » SQL & PL/SQL » SQL & PL/SQL » Analytic Function: How to Partition Hierarchical Data
Analytic Function: How to Partition Hierarchical Data [message #679566] Mon, 09 March 2020 12:20 Go to next message
Messages: 20
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'
  • Attachment: Results.png
    (Size: 4.23KB, Downloaded 159 times)
Re: Analytic Function: How to Partition Hierarchical Data [message #679568 is a reply to message #679566] Mon, 09 March 2020 13:03 Go to previous message
Michel Cadot
Messages: 67884
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.

Previous Topic: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block (merged)
Next Topic: multiple inserts without Loops
Goto Forum:

Current Time: Fri Jun 18 07:49:48 CDT 2021