sqlmysqlselectstored-proceduresdql

Need Between Range rows between using previous end_date and next start_date


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


Solution

  • 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