A more complex Island & Gaps problem in SQL

Question

First of all i would like to apologize if this was already discussed, but after 4 hours of searching I wasn't able to find anything which could help me with my problem.

This is the scenario: there are towers where different clients can have different equipment mounted at different heights on that tower (like the tower in telecom).

These are the facts:

• the tower has 4 sides and the equipment can be mounted (for the same height) only on 2 opposite sides. So we cannot have equipment mounted (at the same height) on 3 different sides of the tower not on 2 sides which are near. I hope I'm explaining ok.

• each client has its own range from the total height of an tower.

The requirement is to determine what would be the availability for each client in that tower. If an client has an equipment mounted only on one side, it would be considered Partial Available; if has no equipment at all it would be considered Available. If the customer has equipment mounted on bot opposite sides of the tower, that height it will be considered not available.

These would be the data we are working on:

create table tower_test
(Tower_Number        VARCHAR2(12),
 Tower_Side          VARCHAR2(1),
 Tower_Height        NUMBER,
 Tower_Height_Um     VARCHAR2(1) default 'm',
 Client              VARCHAR2(25),
 Client_Start_Height NUMBER,
 Range_From          NUMBER,
 Range_To            NUMBER);
 
-- No Client

-- Side A
-- Client 1 
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_1', 12, 17, 18);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_1', 12, 22, 23);
-- Client 2
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_2', 24, 35, 36);
-- Client 3
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_3', 40, 40, 41);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_3', 40, 47, 48);

-- Side B
-- Client 1
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_1', 12, 13, 14);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_1', 12, 19, 20);
-- Client 2
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_2', 24, 31, 32);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_2', 24, 37, 38);
-- Client 3
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_3', 40, 43, 44);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_3', 40, 46, 47);

-- Side C
-- Client 1 
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_1', 12, 17, 18);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_1', 12, 22, 23);
-- Client 2
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_2', 24, 28, 29);
-- Client 3
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_3', 40, 40, 41);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_3', 40, 47, 48);

-- Side D
-- Client 1
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'D', 50, 'Client_1', 12, 19, 20);
-- Client 2
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'D', 50, 'Client_2', 24, 31, 32);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'D', 50, 'Client_2', 24, 37, 38);
-- Client 3
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'D', 50, 'Client_3', 40, 46, 47);

COMMIT;

SELECT * FROM tower_test;

So, starting from this set of data:

I would need this format:

Is there any way to get that in Oracle SQL using Gap & Island approach? If yes, can you explain me how to achieve it?

Thanks!

Answer 1

This would probably be easier if you had your data in third normal form and the data in four tables:

tower (tower_number, tower_height, tower_height_um)
tower_sides (tower_number, tower_side)
tower_client (tower_number, client, client_start_height, client_end_height)
tower_equipment(tower, tower_side, client, range_from, range_to)

However, it is possible to split it into those components from your table and use:

SELECT tower_number,
       tower_height,
       tower_height_um,
       client,
       CASE availability
       WHEN 0 THEN 'T'
       WHEN 1 THEN 'P'
       END AS availability,
       range_from,
       range_to
FROM   (
SELECT h.tower_number,
       MAX(h.tower_height) AS tower_height,
       MAX(h.tower_height_um) AS tower_height_um,
       h.height,
       MAX(ch.client) AS client,
       COUNT(t.tower_side) AS availability
FROM   (
         -- Generate all the heights and sides
         WITH heights (tower_number, tower_side, height, tower_height, tower_height_um) AS (
           SELECT tower_number,
                  tower_side,
                  0,
                  MAX(tower_height),
                  MAX(tower_height_um) KEEP (DENSE_RANK LAST ORDER BY tower_height)
           FROM   tower_test
           GROUP BY tower_number, tower_side
         UNION ALL
           SELECT tower_number,
                  tower_side,
                  height + 1,
                  tower_height,
                  tower_height_um
           FROM   heights
           WHERE  height < tower_height
         )
         SELECT * FROM heights
       ) h
       LEFT OUTER JOIN (
         -- Include the client heights
         SELECT *
         FROM   (
           SELECT DISTINCT
                  tower_number,
                  client,
                  client_start_height AS start_height,
                  LEAD(client_start_height - 1, 1, tower_height) OVER (
                    PARTITION BY tower_number
                    ORDER BY client_start_height, range_to
                  ) AS end_height
           FROM   tower_test
         )
         WHERE  start_height < end_height
       ) ch
       ON (    h.tower_number = ch.tower_number
           AND h.height BETWEEN ch.start_height AND ch.end_height)
       -- Include the equipment ranges
       LEFT OUTER JOIN tower_test t
       ON (    t.tower_number = h.tower_number
           AND t.tower_side = h.tower_side
           AND h.height BETWEEN t.range_from AND t.range_to)
GROUP BY
      h.tower_number,
      h.height
)
MATCH_RECOGNIZE(
  PARTITION BY tower_number
  ORDER BY height
  MEASURES
    FIRST(tower_height) AS tower_height,
    FIRST(tower_height_um) AS tower_height_um,
    FIRST(client) AS client,
    FIRST(availability) AS availability,
    FIRST(height) AS range_from,
    LAST(height) AS range_to
  ONE ROW PER MATCH
  PATTERN (client_available+)
  DEFINE 
    client_available
      AS (FIRST(client) = client OR FIRST(client) IS NULL AND client IS NULL)
      AND FIRST(availability) = availability
)
WHERE availability < 2

Which, for your sample data, outputs:

TOWER_NUMBER	TOWER_HEIGHT	TOWER_HEIGHT_UM	CLIENT	AVAILABILITY	RANGE_FROM	RANGE_TO
123456_TWR1	50	m	null	T	0	11
123456_TWR1	50	m	Client_1	T	12	12
123456_TWR1	50	m	Client_1	P	13	14
123456_TWR1	50	m	Client_1	T	15	16
123456_TWR1	50	m	Client_1	T	21	21
123456_TWR1	50	m	Client_2	T	24	27
123456_TWR1	50	m	Client_2	P	28	29
123456_TWR1	50	m	Client_2	T	30	30
123456_TWR1	50	m	Client_2	T	33	34
123456_TWR1	50	m	Client_2	P	35	36
123456_TWR1	50	m	Client_2	T	39	39
123456_TWR1	50	m	Client_3	T	42	42
123456_TWR1	50	m	Client_3	P	43	44
123456_TWR1	50	m	Client_3	T	45	45
123456_TWR1	50	m	Client_3	T	49	50

db<>fiddle here