By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS reference_log;
CREATE TABLE reference_log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
reference_number VARCHAR(20) NOT NULL,
reference_date DATE NOT NULL,
log_date DATE NOT NULL
) Engine=InnoDB;
INSERT INTO reference_log (id,reference_number,reference_date,log_date) VALUES
(1, '123','2024-04-01','2024-04-14');
INSERT INTO reference_log (id,reference_number,reference_date,log_date) VALUES
(2, '123','2024-04-01','2024-04-15');
INSERT INTO reference_log (id,reference_number,reference_date,log_date) VALUES
(3, '123','2024-04-01','2024-05-01');
INSERT INTO reference_log (id,reference_number,reference_date,log_date)VALUES
(4, '123','2024-05-01','2024-05-06');
select * from reference_log;
| id | reference_number | reference_date | log_date |
|---|---|---|---|
| 1 | 123 | 2024-04-01 | 2024-04-14 |
| 2 | 123 | 2024-04-01 | 2024-04-15 |
| 3 | 123 | 2024-04-01 | 2024-05-01 |
| 4 | 123 | 2024-05-01 | 2024-05-06 |
select *
from(
select *
,coalesce(lag(reference_date)
over(partition by reference_number order by id)
,reference_date)prev_ref_date
from reference_log
)logprev
where reference_date<>prev_ref_date
and log_date<=cast('2024-05-06' as date)
| id | reference_number | reference_date | log_date | prev_ref_date |
|---|---|---|---|---|
| 4 | 123 | 2024-05-01 | 2024-05-06 | 2024-04-01 |