Connect By query with recurring hierarchical checks

Question

I have written the following query and unfortunately it does not work. All I am trying to achieve is that the dates that I select with each row is the effective date of the customer relationship.

The ci_per_per table contains the following columns:

per_id1, per_id2, start_dt, end_dt

per_id1 is the parent, per_id2 is the child.

start_dt and end_dt define the duration of the relationship

SELECT
  CONNECT_BY_ROOT per_id2                                                           AS per_id2,
  per_id1,
  (CASE WHEN start_dt < (PRIOR start_dt1)
            THEN (PRIOR start_dt1) 
            ELSE start_dt 
        END) AS start_dt1,
  (CASE WHEN end_dt > (PRIOR end_dt1) 
             THEN (PRIOR end_dt1) 
             ELSE end_dt 
        END)         AS end_dt1,
  level                                                                             AS "PER_LEVEL"
  FROM ci_per_per
  CONNECT BY NOCYCLE PRIOR per_id1 = per_id2

The error I am getting for the sql is: END_DT1: Invalid identifier.

Is there a way to achieve this functionality in oracle?

Example scenario:

per_id1 | per_id2 |   start_dt  |   end_dt
-------------------------------------------
B       |   A     |  01-01-2011 | 01-01-2011
C       |   B     |  01-01-2010 | 01-01-2010
E       |   B     |  01-01-2011 | 01-01-2014
D       |   C     |  01-01-2009 | 01-01-2015

now I want all the records to be having 01-01-2011 as thier start date. However, if the hierarchy is reversed(meaning exchange per_id_1 and Per_id2 values), then the records should be displayed as they are.

Note that for the above example, we need to run it with the start with clause with per_id2='A' condition. Also, understand that the results might change if you run the query with the per_id2='B'

Thus, when I start with per_id2='B' the result should make all the records to be 01-01-2010 and not 01-01-2011

now with the end date, it should be 01-01-2011 for all records except the one with E and the one with A. where the end date will be null (assuming we start with A).

Answer 1

I have been trying to work out the solution to this problem and have found a solution.

Please correct me if you think there is any issue with this below SQL:

select *
  from (SELECT root as per_id2,
               per_id1,
               PER_LEVEL,
               (case
                 when max(connect_by_root start_dt) >= max(start_dt) then
                  max(connect_by_root start_dt)
                 else
                  max(start_dt)
               end) as max_start,
               (case
                 when min(connect_by_root end_dt) <= min(end_dt) then
                  min(connect_by_root end_dt)
                 else
                  min(end_dt)
               end) AS min_end
          FROM (SELECT connect_by_root per_id2 AS root,
                       per_id1,
                       per_id2,
                       level as per_level,
                       (case
                         when connect_by_root start_dt >= start_dt then
                          connect_by_root start_dt
                         else
                          start_dt
                       end) as start_dt,
                       (case
                         when connect_by_root end_dt <= end_dt then
                          connect_by_root end_dt
                         else
                          end_dt
                       end) as end_dt
                  FROM ci_per_per
                CONNECT BY NOCYCLE PRIOR per_id1 = per_id2)
        CONNECT BY NOCYCLE PRIOR per_id1 = per_id2
               and PRIOR root = root
         GROUP BY per_id1, per_id2, root, per_level)
 where max_start <= min_end 

Here's the fiddle for the same

Answer 2

A combination of an Oracle Hierarchy and CTE will produce the result you want:

WITH cte AS 
(SELECT 
  per_id1,
  max(connect_by_root start_dt) as max_start,
  min(connect_by_root end_dt) as min_end
FROM 
  ci_per_per
CONNECT BY 
  NOCYCLE PRIOR per_id1 = per_id2
GROUP BY 
  per_id1)
SELECT
  ci_per_per.per_id1,
  ci_per_per.per_id2,
  cte.max_start,
  cte.min_end
FROM
  cte
  join ci_per_per on cte.per_id1=ci_per_per.per_id1

See SQLFiddle here Your example is fairly simple, so I can't be certain that this logic will work for all of your test cases.

EDIT updated for lowest end_dt.