Skip to Main Content
  • Questions
  • hierarchical query with date consideration

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Steve.

Asked: July 16, 2018 - 12:42 pm UTC

Last updated: July 20, 2018 - 9:21 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

How to include an effectivity date range check in hierarchical query? Such that a loop may exist if the date range is excluded, but considering the range a loop would not.

with
output_tab as (select 'P1' as output_id, 100 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
               union all
               select 'P2' as output_id, 200 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
               union all
               select 'P3' as output_id, 300 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
               union all
               select 'P4' as output_id, 400 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
               union all
               select 'P5' as output_id, 500 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual),
input_tab as (select 'C8' as input_id, 'P1' as output_id, 1111 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'C9' as input_id, 'P1' as output_id, 2222 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'C12' as input_id,'P1' as output_id, 3333 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'C5' as input_id, 'P2' as output_id, 4444 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'C7' as input_id, 'P2' as output_id, 5555 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'P1' as input_id, 'P2' as output_id, 100 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('09/17/2018', 'MM/DD/YYYY') as end_date from dual  --ends
              union all
              select 'C6' as input_id, 'P3' as output_id, 9999 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'C10' as input_id, 'P3' as output_id, 1122 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'C1' as input_id, 'P4' as output_id, 6666 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'C2' as input_id, 'P4' as output_id, 7777 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'C4' as input_id, 'P4' as output_id, 8888 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'P2' as input_id, 'P4' as output_id, 200 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'P4' as input_id, 'P5' as output_id, 400 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'P3' as input_id, 'P5' as output_id, 300 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
              union all
              select 'P5' as input_id, 'P1' as output_id, 500 as input_key, to_date('09/18/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual -- starts
              )
select distinct ot.output_id, ot.output_key, it.input_key, it.input_id, it.start_date, it.end_date
from output_tab ot inner join input_tab it on (ot.output_id = it.output_id)
start with ot.output_id = 'P1'
connect by it.input_key = prior ot.output_key
order by 1, 2


Above, the last union (with "-- starts") creates the loop and the query fails; however, its start date is after the ending of another entry (with "-- ends"), so I want to be able to see that as not being a loop.

This check is desired to done against configuration a user in putting into the UI prior to commiting, the heirarchy could be complicated and we want to know if a loop exists (the user may have made a mistake). Currently, the best option I'm entertaining is using a loop to check each date inidividually for the parents effectivity range, but I'm hoping there is a better way.

Thanks,
Steve

with LiveSQL Test Case:

and Chris said...

You need to add conditions to your connect by so it doesn't revisit the earlier row.

Based on the data shown, you can do this by checking that the current start_date is on or after the previous:

start_date >= prior start_date


But I suspect this is a feature of your sample data. If P1 starting 18 Sept has a child row, would its start date have to be on 18 Sept or after too? If no, you'll have to find something else to check against.

Also, starting with just P1 means you'll get more root for this than you need. You can filter this down to the first by checking the current start_date equals the minimum for the output_key:

with 
output_tab as (
  select 'P1' as output_id, 100 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P2' as output_id, 200 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P3' as output_id, 300 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P4' as output_id, 400 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P5' as output_id, 500 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual), 
input_tab as (
select 'C8' as input_id, 'P1' as output_id, 1111 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C9' as input_id, 'P1' as output_id, 2222 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C12' as input_id,'P1' as output_id, 3333 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C5' as input_id, 'P2' as output_id, 4444 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C7' as input_id, 'P2' as output_id, 5555 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P1' as input_id, 'P2' as output_id, 100 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('09/17/2018', 'MM/DD/YYYY') as end_date from dual  --ends 
  union all 
  select 'C6' as input_id, 'P3' as output_id, 9999 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C10' as input_id, 'P3' as output_id, 1122 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C1' as input_id, 'P4' as output_id, 6666 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C2' as input_id, 'P4' as output_id, 7777 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C4' as input_id, 'P4' as output_id, 8888 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P2' as input_id, 'P4' as output_id, 200 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P4' as input_id, 'P5' as output_id, 400 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P3' as input_id, 'P5' as output_id, 300 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P5' as input_id, 'P1' as output_id, 500 as input_key, to_date('09/18/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual -- starts 
  ) , t as (
select ot.output_id, ot.output_key, it.input_key, it.input_id, it.start_date, it.end_date , 
       min ( it.start_date ) over ( partition by ot.output_id ) mn_start
from output_tab ot inner join input_tab it on (ot.output_id = it.output_id)
)
select level, t.* from t
start with output_id = 'P1' 
and   mn_start = start_date
connect by input_key = prior output_key 
        and start_date >= prior start_date
order by 1, 2;

LEVEL   OUTPUT_ID   OUTPUT_KEY   INPUT_KEY   INPUT_ID   START_DATE    END_DATE      MN_START      
      1 P1                   100        1111 C8         01-JAN-2018   31-DEC-2020   01-JAN-2018   
      1 P1                   100        3333 C12        01-JAN-2018   31-DEC-2020   01-JAN-2018   
      1 P1                   100        2222 C9         01-JAN-2018   31-DEC-2020   01-JAN-2018   
      2 P2                   200         100 P1         01-JAN-2018   17-SEP-2018   01-JAN-2018   
      2 P2                   200         100 P1         01-JAN-2018   17-SEP-2018   01-JAN-2018   
      2 P2                   200         100 P1         01-JAN-2018   17-SEP-2018   01-JAN-2018   
      3 P4                   400         200 P2         01-JAN-2018   31-DEC-2020   01-JAN-2018   
      3 P4                   400         200 P2         01-JAN-2018   31-DEC-2020   01-JAN-2018   
      3 P4                   400         200 P2         01-JAN-2018   31-DEC-2020   01-JAN-2018   
      4 P5                   500         400 P4         01-JAN-2018   31-DEC-2020   01-JAN-2018   
      4 P5                   500         400 P4         01-JAN-2018   31-DEC-2020   01-JAN-2018   
      4 P5                   500         400 P4         01-JAN-2018   31-DEC-2020   01-JAN-2018   
      5 P1                   100         500 P5         18-SEP-2018   31-DEC-2020   01-JAN-2018   
      5 P1                   100         500 P5         18-SEP-2018   31-DEC-2020   01-JAN-2018   
      5 P1                   100         500 P5         18-SEP-2018   31-DEC-2020   01-JAN-2018

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks.

Steve M, July 19, 2018 - 1:00 pm UTC

Thanks, I'll play around with this and see if I can get it to work with our needs.

The user will be creating a hierarchy manually (one "output" at a time), so the dates used for effectivity could be anything; we are not validating across inputs/outputs, only that then end is greater than start for any single range.

When our reporting process runs that will use the data, it qualifies by a single date, 09/19/2018 for example, to get everything appropriate.

Thanks again.
Chris Saxon
July 20, 2018 - 9:21 am UTC

If you only need to validate that the dates for a specific output are non-overlapping, you may be better with an unpivot. Use this to convert the start/end date values to a single column:

with input_tab as (
select 'C8' as input_id, 'P1' as output_id, 1111 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C9' as input_id, 'P1' as output_id, 2222 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C12' as input_id,'P1' as output_id, 3333 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C5' as input_id, 'P2' as output_id, 4444 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C7' as input_id, 'P2' as output_id, 5555 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P1' as input_id, 'P2' as output_id, 100 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('09/17/2018', 'MM/DD/YYYY') as end_date from dual  --ends 
  union all 
  select 'C6' as input_id, 'P3' as output_id, 9999 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C10' as input_id, 'P3' as output_id, 1122 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C1' as input_id, 'P4' as output_id, 6666 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C2' as input_id, 'P4' as output_id, 7777 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'C4' as input_id, 'P4' as output_id, 8888 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P2' as input_id, 'P4' as output_id, 200 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P4' as input_id, 'P5' as output_id, 400 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P3' as input_id, 'P5' as output_id, 300 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual 
  union all 
  select 'P5' as input_id, 'P1' as output_id, 500 as input_key, to_date('09/18/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual -- starts 
  )
 select * from input_tab
 unpivot (
   dt for col in ( start_date, end_date )
 )
 order  by output_id, dt;

INPUT_ID   OUTPUT_ID   INPUT_KEY   COL          DT              
C9         P1                 2222 START_DATE    01-JAN-2018    
C12        P1                 3333 START_DATE    01-JAN-2018    
C8         P1                 1111 START_DATE    01-JAN-2018    
P5         P1                  500 START_DATE    18-SEP-2018    
C12        P1                 3333 END_DATE      31-DEC-2020    
C9         P1                 2222 END_DATE      31-DEC-2020    
P5         P1                  500 END_DATE      31-DEC-2020    
C8         P1                 1111 END_DATE      31-DEC-2020    
C5         P2                 4444 START_DATE    01-JAN-2018    
C7         P2                 5555 START_DATE    01-JAN-2018    
P1         P2                  100 START_DATE    01-JAN-2018    
P1         P2                  100 END_DATE      17-SEP-2018    
C5         P2                 4444 END_DATE      31-DEC-2020    
C7         P2                 5555 END_DATE      31-DEC-2020    
C6         P3                 9999 START_DATE    01-JAN-2018    
C10        P3                 1122 START_DATE    01-JAN-2018    
C6         P3                 9999 END_DATE      31-DEC-2020    
C10        P3                 1122 END_DATE      31-DEC-2020    
C1         P4                 6666 START_DATE    01-JAN-2018    
C4         P4                 8888 START_DATE    01-JAN-2018    
P2         P4                  200 START_DATE    01-JAN-2018    
C2         P4                 7777 START_DATE    01-JAN-2018    
C1         P4                 6666 END_DATE      31-DEC-2020    
C4         P4                 8888 END_DATE      31-DEC-2020    
C2         P4                 7777 END_DATE      31-DEC-2020    
P2         P4                  200 END_DATE      31-DEC-2020    
P3         P5                  300 START_DATE    01-JAN-2018    
P4         P5                  400 START_DATE    01-JAN-2018    
P3         P5                  300 END_DATE      31-DEC-2020    
P4         P5                  400 END_DATE      31-DEC-2020


You can then use this to validate that starts are before ends. I'm not sure how this works on your data though.

Stew Ashton has several articles discussing this topic, I suggest reading his blog to get some background:

https://stewashton.wordpress.com/2014/03/11/sql-for-date-ranges-gaps-and-overlaps/
https://stewashton.wordpress.com/2014/03/22/overlapping-ranges-with-priority/

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.