SQL Server 2017

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

CREATE TABLE table_prices
(
    
    room_id character,
    check_in date,
    price integer,
    observation_date date
)
insert into table_prices (room_id, check_in, price, observation_date) values
('1', '2019-05-01', 100, '2019-01-01'),
('1', '2019-05-01', 100, '2019-01-02'),
('1', '2019-05-01', 100, '2019-01-03'),
('1', '2019-05-01', 150, '2019-01-04'),
('1', '2019-05-01', 150, '2019-01-05'),
('1', '2019-05-01', 150, '2019-01-06'),
('1', '2019-05-01', 150, '2019-01-07'),
('1', '2019-05-01', 100, '2019-01-08'),
('1', '2019-05-01', 100, '2019-01-09'),
('2', '2019-05-01', 200, '2019-01-01'),
('2', '2019-05-01', 200, '2019-01-02'),
('2', '2019-05-01', 200, '2019-01-03'),
('2', '2019-05-01', 200, '2019-01-04'),
('2', '2019-05-01', 200, '2019-01-05'),
('2', '2019-05-01', 200, '2019-01-06'),
('2', '2019-05-01', 200, '2019-01-07'),
('2', '2019-05-01', 200, '2019-01-08'),
('2', '2019-05-01', 200, '2019-01-09')
18 rows affected

select room_id, check_in,min(observation_date) first_observation,max(observation_date)
last_observation,price
from
(
select *,island=row_number() over(partition by room_id order by observation_date) - 
row_number() over(partition by room_id, price order by observation_date) 
from table_prices
)A group by room_id, check_in,island,price


room_id
check_in
first_observation
last_observation
price


1
01/05/2019 00:00:00
01/01/2019 00:00:00
03/01/2019 00:00:00
100

1
01/05/2019 00:00:00
04/01/2019 00:00:00
07/01/2019 00:00:00
150

1
01/05/2019 00:00:00
08/01/2019 00:00:00
09/01/2019 00:00:00
100

2
01/05/2019 00:00:00
01/01/2019 00:00:00
09/01/2019 00:00:00
200

room_id	check_in	first_observation	last_observation	price
1	01/05/2019 00:00:00	01/01/2019 00:00:00	03/01/2019 00:00:00	100
1	01/05/2019 00:00:00	04/01/2019 00:00:00	07/01/2019 00:00:00	150
1	01/05/2019 00:00:00	08/01/2019 00:00:00	09/01/2019 00:00:00	100
2	01/05/2019 00:00:00	01/01/2019 00:00:00	09/01/2019 00:00:00	200