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
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;