Need Between Range rows between using previous end_date and next start_date

Question

While working white Availability of Resources. I got struct here. Let me explain so expect to get some help here.

Considering a table Availability as below create script with sample records for better understanding.

CREATE TABLE availibility ( 
   id int NOT NULL,
   start_date date NOT NULL, 
   end_date date DEFAULT NULL, 
   status tinyint NOT NULL DEFAULT '1'
) ENGINE=InnoDB;

INSERT INTO availibility (id, start_date, end_date, status) VALUES (1,'2024-02-01', '2024-02-10', 1), (2,'2024-02-21','2024-02-29',1);

Now it returns result as below:

ID	Start Date	End Date	Status
1	2024-02-01	2024-02-10	1
2	2024-02-21	2024-02-29	1

While my expectations is to return in between row as well. Sample below.

ID	Start Date	End Date	Status
1	2024-02-01	2024-02-10	1
NULL	2024-02-11	2024-02-20	0
2	2024-02-21	2024-02-29	1

Help will be appreciate. Thanks

Answer 1

I have change my answer

Of course! If you've modified the query so that the ID doesn't play a role anymore, and it's possible for a smaller ID to appear much later in the sequence, let's test it. Could you please provide the modified query or any specific changes you've made to it? Once I have that information, I'll be able to assist you further and test it accordingly.

(SELECT * FROM availibility order by start_date)
  UNION ALL
( WITH ranked_end_date AS (
    SELECT *,LEAD(start_date) OVER (ORDER BY start_date) AS next_start_date
    FROM availibility
)
SELECT NULL AS id, end_date + INTERVAL 1 DAY , next_start_date - INTERVAL 1 DAY, 0 AS status
FROM ranked_end_date
WHERE next_start_date is not null
AND DATEDIFF(next_start_date, end_date) > 1 )
ORDER BY start_date;

https://dbfiddle.uk/5oi4khgB

Answer 2

Another way is, using UNION:

SELECT 
  id,
  start_date,
  end_date,
  status
FROM availibility

UNION ALL

SELECT 
  NULL, 
  DATE_ADD(a1.end_date,INTERVAL 1 DAY),
  DATE_ADD(a2.start_date, INTERVAL -1 DAY),
  0
FROM availibility a1 
INNER JOIN availibility a2 ON a2.id=2
WHERE a1.id=1
ORDER BY start_date

see: DBFIDDLE

EDIT: Added the comment from @RushabhMadhu, in a readable format:

(SELECT * FROM availibility order by start_date) 
  UNION ALL 
( WITH ranked_end_date AS ( 
     SELECT *,LEAD(start_date) OVER (ORDER BY start_date) AS next_start_date 
     FROM availibility ) 
SELECT 
  NULL AS id, 
  end_date + INTERVAL 1 DAY , 
  next_start_date - INTERVAL 1 DAY, 
  0 AS status 
FROM ranked_end_date 
WHERE next_start_date is not null AND DATEDIFF(next_start_date, end_date) > 1 ) 
ORDER BY start_date

see: DBFIDDLE2