SQL-Oracle Update table using recursivity

Question

Lets assume we have a table like this one:

+--------+------------+---------------+----------------+
| Name   | Position   | Initial Date  | Final Date     |
+--------+------------+---------------+----------------+
| XXX    | 1          | 2016/06/07    | 2016/06/08     |
| XXX    | 2          | 2016/06/08    | 2016/06/09     |
| XXX    | 3          | 2016/06/09    | 2016/06/10     |
| XXX    | 4          | 2016/06/13    | 2016/06/14     |
| XXX    | 6          | 2016/06/14    | 2016/06/15     |
| YYY    | 1          | 2016/06/02    | 2016/06/03     |
+--------+------------+---------------+----------------+

I want to update it adding a new field which indicates the first position of a group. Forming part of a group means that follows these rules:

1. Share the same name
2. Position numbers must be correlatives (Ex: Position 4 and 6 need a number 5 to create a group).
3. The final date of first row must coincide with the initial date of the second one, and so on.

Having all of this in consideration, this should be the outcome:

+--------+------------+---------------+----------------+------------+
| Name   | Position   | Initial Date  | Final Date     | New field  |
+--------+------------+---------------+----------------+------------+
| XXX    | 1          | 2016/06/07    | 2016/06/08     | 1          |
| XXX    | 2          | 2016/06/08    | 2016/06/09     | 1          |
| XXX    | 3          | 2016/06/09    | 2016/06/10     | 1          |
| XXX    | 4          | 2016/06/13    | 2016/06/14     | 4          |
| XXX    | 6          | 2016/06/14    | 2016/06/15     | 6          |
| YYY    | 1          | 2016/06/02    | 2016/06/03     | 1          |
+--------+------------+---------------+----------------+------------+

I can make it work only on groups of 2 members, but I do not know how to approach it in a more than 2 members situation.

This is an example code I used, which obviously does not work for big groups.

update table1 f1
set f1.new_field = NVL((select f2.position
                    from table1 f2
                    where f1.name = f2.name and
                    f2.position = f1.position+1 and
                    f1.final_date = f2.initial_date),f1.position);

Should I use recursive queries to solve this? I don't know how to implement it in SQL in this situation.

Any help is well appreciated!

Answer 1

You can do this using a series of analytic functions, like so:

with sample_data as (select 'XXX' name, 1 position, to_date('07/06/2016', 'dd/mm/yyyy') initial_date, to_date('08/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'XXX' name, 2 position, to_date('08/06/2016', 'dd/mm/yyyy') initial_date, to_date('09/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'XXX' name, 3 position, to_date('09/06/2016', 'dd/mm/yyyy') initial_date, to_date('10/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'XXX' name, 4 position, to_date('13/06/2016', 'dd/mm/yyyy') initial_date, to_date('14/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'XXX' name, 6 position, to_date('14/06/2016', 'dd/mm/yyyy') initial_date, to_date('15/06/2016', 'dd/mm/yyyy') final_date from dual union all
                     select 'YYY' name, 1 position, to_date('02/06/2016', 'dd/mm/yyyy') initial_date, to_date('03/06/2016', 'dd/mm/yyyy') final_date from dual)
-- end of mimicking a table called "sample_data" containing your data
select name,
       position,
       initial_date,
       final_date,
       min(position) over (partition by name, grp_sum) new_field
from   (select name,
               position,
               initial_date,
               final_date,
               sum(change_grp_required) over (partition by name order by position) grp_sum
        from   (select name,
                       position,
                       initial_date,
                       final_date,
                       case when position - lag(position, 1, position) over (partition by name order by position) != 1
                                 or initial_date != lag(final_date, 1, initial_date - 1) over (partition by name order by position) then 1
                            else 0
                       end change_grp_required
                from   sample_data));

NAME   POSITION INITIAL_DATE FINAL_DATE  NEW_FIELD
---- ---------- ------------ ---------- ----------
XXX           1 2016/06/07   2016/06/08          1
XXX           2 2016/06/08   2016/06/09          1
XXX           3 2016/06/09   2016/06/10          1
XXX           4 2016/06/13   2016/06/14          4
XXX           6 2016/06/14   2016/06/15          6
YYY           1 2016/06/02   2016/06/03          1

The innermost subquery determines whether the position and dates of the current and previous row are correlated. If they aren't, then it puts 1, otherwise it puts 0.

The next subquery then calculates a running sum across these numbers - this has the effect of creating the same number for correlated rows (eg. 1 for positions 1 to 3, 2 for position 4 and 3 for position 6) which we can then use to group against.

The outer query then simply finds the minimum position number per name and the newly created grouping column.

You could then use this query in your update statement to do the actual update (obviously, you wouldn't need the initial sample_data subquery, as you'd just use your table_name in the rest of the query directly).