SQL special group in Teradata

Question

I have this data in a table:

service | vat | ini_date | end_date
  1        A   10/01/2017 31/01/2017
  2        A   15/01/2017 28/02/2017
  3        A   15/02/2017 31/03/2017
  4        B   15/01/2017 31/03/2017
  5        B   15/02/2017 31/03/2017
  6        B   20/04/2017 31/05/2017
  7        B   20/05/2017 31/05/2017

And I have to group by VAT services which have match in time.

My output should by this:

group service | vat | ini_date | end_date
  1    1        A   10/01/2017 31/01/2017
  1    2        A   15/01/2017 28/02/2017
  1    3        A   15/02/2017 31/03/2017
  1    4        B   15/01/2017 31/03/2017
  1    5        B   15/02/2017 31/03/2017
  2    6        B   20/04/2017 31/05/2017
  2    7        B   20/05/2017 31/05/2017

I can't use procedures, I'm trying with some analytical function but there is a lot of casuistics. The problem starts when you have more than three services that coincide in time individually.

Any idea is welcome. Thanks

Answer 1

It seems you incrementing for each "block" of time, so one has to detect a "gap" in the dates. Below I have used a Postgres fiddle using window functions LEAD() to discover if there is a gap which is then arranged into the final output using DENSE_RANK(). In the second cte "common_dates" a correlated subquery is used to locate a date when the next gap has been detected. This information is then used to order the final DENSE_RANK(). There may be a more efficient method in Teradata to execute this subquery but I'm not able to trial that).

NB: I have assumed "service" can be used for ordering in the LEAD() function and in the correlated subquery.

Notes: "group" isn't something I would recommend as a column name so I used "group_num" instead and I extended the sample data for testing the query against.

Demo (PostgreSQL 9.6)

CREATE TABLE Table1
    ("service" int, "vat" varchar(1), "ini_date" date, "end_date" date)
;

INSERT INTO Table1
    ("service", "vat", "ini_date", "end_date")
VALUES
    (11, 'A', '2016-01-10 00:00:00', '2016-01-31 00:00:00'),
    (21, 'A', '2016-01-15 00:00:00', '2016-02-28 00:00:00'),
    (31, 'A', '2016-02-15 00:00:00', '2016-03-31 00:00:00'),
    (41, 'B', '2016-01-15 00:00:00', '2016-03-31 00:00:00'),
    (51, 'B', '2016-02-15 00:00:00', '2016-03-31 00:00:00'),
    (61, 'B', '2016-04-20 00:00:00', '2016-05-31 00:00:00'),
    (71, 'B', '2016-05-20 00:00:00', '2016-05-31 00:00:00'),

    (91, 'A', '2017-01-10 00:00:00', '2017-01-31 00:00:00'),
    (92, 'A', '2017-01-15 00:00:00', '2017-02-28 00:00:00'),
    (93, 'A', '2017-02-15 00:00:00', '2017-03-31 00:00:00'),
    (94, 'B', '2017-01-15 00:00:00', '2017-03-31 00:00:00'),
    (95, 'B', '2017-02-15 00:00:00', '2017-03-31 00:00:00'),
    (96, 'B', '2017-04-20 00:00:00', '2017-05-31 00:00:00'),
    (97, 'B', '2017-05-20 00:00:00', '2017-05-31 00:00:00')
;

Query 1:

with gapflag as (
      SELECT
        *
      , lag(end_date) over(order by service) - ini_date dfdts
      , max(end_date) over() max_date
      FROM Table1
      )  
, common_dates as (     
      select
        *
        , coalesce(
                (select ini_date 
                 from gapflag t2 
                 where t2.dfdts < 1  and t2.service > gapflag.service
                 order by service limit 1)
              , 
                max_date
            ) grp_date
      from gapflag
   )
select
       dense_rank() over(order by grp_date) group_num
     , service, vat, ini_date, end_date
from common_dates

Results:

| group_num | service | vat |   ini_date |   end_date |
|-----------|---------|-----|------------|------------|
|         1 |      11 |   A | 2016-01-10 | 2016-01-31 |
|         1 |      21 |   A | 2016-01-15 | 2016-02-28 |
|         1 |      31 |   A | 2016-02-15 | 2016-03-31 |
|         1 |      41 |   B | 2016-01-15 | 2016-03-31 |
|         1 |      51 |   B | 2016-02-15 | 2016-03-31 |
|         2 |      61 |   B | 2016-04-20 | 2016-05-31 |
|         2 |      71 |   B | 2016-05-20 | 2016-05-31 |
|         3 |      91 |   A | 2017-01-10 | 2017-01-31 |
|         3 |      92 |   A | 2017-01-15 | 2017-02-28 |
|         3 |      93 |   A | 2017-02-15 | 2017-03-31 |
|         3 |      94 |   B | 2017-01-15 | 2017-03-31 |
|         3 |      95 |   B | 2017-02-15 | 2017-03-31 |
|         4 |      96 |   B | 2017-04-20 | 2017-05-31 |
|         4 |      97 |   B | 2017-05-20 | 2017-05-31 |

Answer 2

It looks you want to group services that overlap in time into groups. Since Since Services 1-5 all overlap (like a chain), they become "Group 1". Since the next service (Service 6) doesn't overlap with the previous group, you give it the next id - "Group 2" and continue. Is that correct?

If so...a couple functions that can help you out:

Window functions (to peek at the previous row)
PERIOD data type + related functions to test for overlap