Postgres 9.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 9.5.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit



SELECT 1


begin;
CREATE temp TABLE Table1 (
    staydate date         NOT NULL,
    type     char(3)      NOT NULL,
    rate      numeric(7,2) NOT NULL,
    cityno   int          NOT NULL,
    roomno   int          NOT NULL,
    CONSTRAINT PK_Table1 PRIMARY KEY 
    ( staydate, cityno, roomno )
);

INSERT INTO Table1 ( staydate, type, rate , cityno, roomno )
VALUES
( '2022-10-03', 'ALL',  10, 5001,        0 ),
( '2022-10-04', 'ALL',  10, 5001,        0 ),
( '2022-10-05', 'ALL', 101, 5001,        0 ),
( '2022-10-06', 'ALL', 101, 5001,        0 ),
( '2022-10-07', 'ALL', 101, 5001,        0 ),
( '2022-10-08', 'ALL', 101, 5001,        0 ),
( '2022-10-09', 'ALL', 101, 5001,        0 ),
( '2022-10-10', 'ALL', 101, 5001,        0 ),
( '2022-10-10', 'ALL', 101, 5001, 10000001 ),
( '2022-10-11', 'ALL',  12, 5001, 10000001 ),
( '2022-10-11', 'ALL',  10, 5001,        0 ),
( '2022-10-12', 'ALL',  12, 5001, 10000001 ),
( '2022-10-12', 'ALL',  10, 5001,        0 ),
( '2022-10-13', 'ALL',  10, 5001,        0 ),
( '2022-10-14', 'ALL',  10, 5001,        0 ),
( '2022-10-15', 'ALL',  10, 5001,        0 );
commit;

COMMIT


create temp view part1 as(
WITH RECURSIVE hit AS (
   (
   SELECT staydate, type, rate,cityno,roomno
   FROM   Table1
   ORDER  BY type, rate,cityno,roomno,staydate
   LIMIT  1
   )
   UNION ALL
   SELECT t.*
   FROM   hit h
   CROSS  JOIN LATERAL (
      SELECT t.staydate, t.type,t.rate,t.cityno,t.roomno
      FROM   Table1 t
      WHERE  ( t.rate,t.roomno, t.cityno,t.staydate) >  (h.rate,h.roomno, h.cityno,h.staydate + interval '7 day') 
      ORDER  BY t.type,t.rate,t.cityno,t.roomno ,staydate
      LIMIT  1
      ) t
   )
SELECT * FROM hit);

CREATE VIEW


create temp view part2 as(
WITH RECURSIVE hit AS (
   (
   SELECT staydate, type, rate,cityno,roomno
   FROM   Table1
   ORDER  BY type desc,rate desc,cityno desc,roomno desc,staydate desc
   LIMIT  1
   )
   UNION ALL
   SELECT t.*
   FROM   hit h
   CROSS  JOIN LATERAL (
      SELECT t.staydate, t.type,t.rate,t.cityno,t.roomno
      FROM   Table1 t
      WHERE  ( t.rate,t.roomno, t.cityno,t.staydate) <  (h.rate,h.roomno, h.cityno,h.staydate - interval '7 day') 
      ORDER  BY t.type desc,t.rate desc,t.cityno desc,t.roomno desc,t.staydate desc
      LIMIT  1
      ) t
   )
SELECT * FROM hit);

CREATE VIEW


select  distinct on (1,3,4,5,6)
    p1.staydate as from_date
    ,p2.staydate as to_date
    ,p1.type
    ,p1.rate
    ,p1.cityno
    ,p1.roomno
from part1 p1 CROSS join part2 p2
where p1.rate = p2.rate and p2.roomno = p1.roomno
and p2.staydate >= p1.staydate
order  by 1,3,4,5,6,2;


from_date
to_date
type
rate
cityno
roomno


2022-10-03
2022-10-04
ALL
10.00
5001
0

2022-10-05
2022-10-10
ALL
101.00
5001
0

2022-10-10
2022-10-10
ALL
101.00
5001
10000001

2022-10-11
2022-10-15
ALL
10.00
5001
0

2022-10-11
2022-10-12
ALL
12.00
5001
10000001



SELECT 5
from_date	to_date	type	rate	cityno	roomno
2022-10-03	2022-10-04	ALL	10.00	5001	0
2022-10-05	2022-10-10	ALL	101.00	5001	0
2022-10-10	2022-10-10	ALL	101.00	5001	10000001
2022-10-11	2022-10-15	ALL	10.00	5001	0
2022-10-11	2022-10-12	ALL	12.00	5001	10000001