Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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);
INSERT INTO tower_test (
  Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To
)
SELECT '123456_TWR1', 'A', 50, 'Client_1', 12, 17, 18 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'A', 50, 'Client_1', 12, 22, 23 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'A', 50, 'Client_2', 24, 35, 36 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'A', 50, 'Client_3', 40, 40, 41 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'A', 50, 'Client_3', 40, 47, 48 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'B', 50, 'Client_1', 12, 13, 14 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'B', 50, 'Client_1', 12, 19, 20 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'B', 50, 'Client_2', 24, 31, 32 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'B', 50, 'Client_2', 24, 37, 38 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'B', 50, 'Client_3', 40, 43, 44 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'B', 50, 'Client_3', 40, 46, 47 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'C', 50, 'Client_1', 12, 17, 18 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'C', 50, 'Client_1', 12, 22, 23 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'C', 50, 'Client_2', 24, 28, 29 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'C', 50, 'Client_3', 40, 40, 41 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'C', 50, 'Client_3', 40, 47, 48 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'D', 50, 'Client_1', 12, 19, 20 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'D', 50, 'Client_2', 24, 31, 32 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'D', 50, 'Client_2', 24, 37, 38 FROM DUAL UNION ALL
SELECT '123456_TWR1', 'D', 50, 'Client_3', 40, 46, 47 FROM DUAL;
20 rows affected

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   (
         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 (
         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)
       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


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

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