Question 348696 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search