Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions

Lucas,

You said “I would be interested in learning about yours”
Ok, here goes.

I too accepted the challenge, and tried to build a solution without first taking a sneak peek at yours.
I hoped to be able to solve it, and end up with a similar solution.
Like you said: “SQL challenges typically can be dealt with in a number of ways”.
I ended up with a query that (in my very humble and irrelevant opinion) is somewhat simpler, and therefor perhaps easier to understand for a developer who didn’t build it, but is forced to do maintenance on it.
I’d hereby like to offer you my solution.

I’m not trying to say my solution is better then yours.
In fact, I can imagine that I overlooked something.
Although my query produces the same end-result, and although I did my best to implement the requirements, not to produce the desired data, There might still be something wrong with it.
If so, I’d like to hear about it.

I added some extra records (policy_id = 3) to your data for 2 test-cases that I thought should be in there.
a) 3 periods that overlap: record 2 overlaps 1, 3 overlaps 2 but 3 does not overlap 1
b) 2 periods that do not overlap, but do ‘connect’: period 1 ends at date X and period 2 starts at X+1
(in fact your query sees the last case as 2 separate periods)

My added data:
3 overlapping periods

insert into policy_periods values (3, to_date('01-01-2012','DD-MM-YYYY'), to_date('01-06-2012','DD-MM-YYYY'), 'I');
insert into policy_periods values (3, to_date('01-03-2012','DD-MM-YYYY'), to_date('01-09-2012','DD-MM-YYYY'), 'I');
insert into policy_periods values (3, to_date('01-08-2012','DD-MM-YYYY'), to_date('01-11-2012','DD-MM-YYYY'), 'I');

2 connecting periods
insert into policy_periods values (3, to_date(’15-11-2012′,’DD-MM-YYYY’), to_date(’15-12-2012′,’DD-MM-YYYY’), ‘I’);
insert into policy_periods values (3, to_date(’16-12-2012′,’DD-MM-YYYY’), to_date(’31-12-2012′,’DD-MM-YYYY’), ‘I’);
commit;

So, what does my query do?
First, like you, I decided that within an ID and type (in or out) I need to fuse periods that overlap (or connect) together.
So in the WITH clause there is query ‘aggregated_periods’.
Here, in the innermost query, I check for each record if it’s period overlaps (or connects) to the previous period (for this id and type).
If it doesn’t, the start_date is selected. If it does, the start_date is selected as being NULL, because I don’t care about that start_date.
Next, from the result-set I select the last_value of this start_date with the ‘ignore nulls’ option. This way all records that ‘belong together’ because they overlap/connect will get the same start_date: the first (and only not-null) one.
Then from this result-set for every record I select the MAX(end_date) for the combination of id, type and start_date.
Now all records that ‘belong together’ have the same start and end date, so a final DISTINCT completes my quest for aggregated periods.

Since the ‘out’ records are dominant (they overrule everything) I need to somehow select the periods that are NOT blocked by any out-period
The query ‘valid_periods’ determines these periods.
For every ‘out’ period in aggregated_periods it selects a period as:
start_date = the day after the end_date of the previous out-period, NULL means ‘the beginning of time’
end_date = the day before the start_date of this out-period, NULL means ‘the end of time’
One extra period is added for the period starting the day after the last ‘out’ end_date until the end of time.

Finally the main query takes the ‘in’ periods from aggregated_periods.
Valid_periods does not return records if there are no ‘out’ periods, so the ‘in’ periods are outer joined to valid_periods that overlap.
If no records are returned by valid_periods this causes a join to NULL values, meaning: ‘from the beginning until the end of time’
Than for every selected record the start_date becomes the greatest of the in- and valid-start_date, en the end_date becomes the least of the in- and valid-end_date

SELECT policy_id
,      in_or_out
,      start_date
,      end_date
FROM   policy_periods
ORDER BY policy_id
,        in_or_out
,        start_date
,        end_date
;

      POLICY_ID I START_DAT END_DATE
--------------- - --------- ---------
              1 I 10-OCT-10 15-MAY-11
              1 I 20-NOV-11 04-JUN-12
              1 I 05-APR-12 12-AUG-12
              1 I 31-OCT-12 01-MAR-13
              1 I 31-JAN-13 01-JUN-13
              1 O 15-OCT-10 19-OCT-10
              1 O 14-FEB-11 10-SEP-11
              1 O 01-FEB-12 15-APR-12
              1 O 01-JUN-12 21-JUL-12
              2 I 20-NOV-10 24-JUN-11
              2 I 05-APR-11 12-AUG-12
              2 O 14-FEB-10 10-SEP-11
              3 I 01-JAN-12 01-JUN-12
              3 I 01-MAR-12 01-SEP-12
              3 I 01-AUG-12 01-NOV-12
              3 I 15-NOV-12 15-DEC-12
              3 I 16-DEC-12 31-DEC-12

17 rows selected.

WITH aggregated_periods
AS   (SELECT DISTINCT
             policy_id
      ,      in_or_out
      ,      start_date
      ,      MAX(end_date) OVER (PARTITION BY policy_id,in_or_out,start_date)  end_date
      FROM   (SELECT policy_id
              ,      in_or_out
              ,      LAST_VALUE (start_date IGNORE NULLS) OVER (PARTITION BY policy_id,in_or_out ORDER BY start_date_org NULLS LAST)   start_date
              ,      end_date
              FROM   (SELECT policy_id
                      ,      in_or_out
                      ,      start_date          start_date_org
                      ,      CASE
                               WHEN start_date -1 <= LAG (end_date) OVER (PARTITION BY policy_id,in_or_out ORDER BY start_date,end_date)
                               THEN NULL         -- overlap with or connecting to previous period, so not interested in this start_date
                               ELSE start_date
                             END                 start_date
                      ,      end_date
                      FROM   policy_periods
                     )
             )
     )
,    valid_periods
AS   (SELECT policy_id
      ,      LAG(end_date) OVER (PARTITION BY policy_id ORDER BY start_date) + 1    start_date
      ,      start_date -1                                                          end_date
      FROM   aggregated_periods
      WHERE  in_or_out        = 'O'
      UNION
      SELECT policy_id
      ,      MAX(end_date) + 1     start_date
      ,      NULL                  end_date
      FROM   aggregated_periods
      WHERE  in_or_out        = 'O'
      GROUP BY policy_id
     )
SELECT per_in.policy_id
,      GREATEST (per_in.start_date
                ,NVL(per_val.start_date
                    ,per_in.start_date
                    )
                )               start_date
,      LEAST    (per_in.end_date
                ,NVL(per_val.end_date
                    ,per_in.end_date
                    )
                )               end_date
FROM   aggregated_periods           per_in
LEFT OUTER JOIN   valid_periods     per_val
  ON   per_val.policy_id       = per_in.policy_id
  -- And valid_period should overlap in-period
  AND  (   per_val.start_date IS NULL
        OR per_val.start_date <= per_in.end_date
       )
  AND  (   per_val.end_date  IS NULL
        OR per_val.end_date   >= per_in.start_date
       )
WHERE  per_in.in_or_out    = 'I'
ORDER BY per_in.policy_id
,        start_date
;

      POLICY_ID START_DAT END_DATE
--------------- --------- ---------
              1 10-OCT-10 14-OCT-10
              1 20-OCT-10 13-FEB-11
              1 20-NOV-11 31-JAN-12
              1 16-APR-12 31-MAY-12
              1 22-JUL-12 12-AUG-12
              1 31-OCT-12 01-JUN-13
              2 11-SEP-11 12-AUG-12
              3 01-JAN-12 01-NOV-12
              3 15-NOV-12 31-DEC-12

9 rows selected.