Re: Find the closest previous date and update matching records from another table

[Joni villa]

Just updated the table and added the script i'm using. The problem I cant get the exact values using my script. by the way' im using MySQL 8.0 CE.

create table Temp_ReturnTable (
id varchar(35),
id2 varchar(35),
returndate datetime,
id_reference varchar(60)
);

insert into Temp_ReturnTable(id, id2, returndate, id_reference)
VALUES
('12345670','123456PH','2022-04-26 00:00:00','2022-04-26 00:00:0012345670'),
('12345678','123456AB','2022-01-31 00:00:00','2022-01-31 00:00:0012345678'),
('12345678','123456AB','2022-02-18 00:00:00','2022-02-18 00:00:0012345678'),
('12345678','123456AB','2022-05-25 00:00:00','2022-05-25 00:00:0012345678'),
('12345650','123456TW','2022-03-01 00:00:00','2022-03-01 00:00:0012345650'),
('22345688','223456PK','2022-01-21 00:00:00','2022-01-21 00:00:0022345688'),
('22345688','223456PK','2022-03-08 00:00:00','2022-03-08 00:00:0022345688');



create table temp_ship (id varchar(35),
id2 varchar(35),
sdt datetime,
id_reference varchar(60)
);
insert into temp_ship(id, id2, sdt, id_reference)
VALUES
('12345670','123456PH','2020-11-26 00:00:00',NULL),
('12345670','123456PH','2022-04-23 00:00:00',NULL),
('12345670','123456PH','2022-07-25 00:00:00',NULL),
('12345678','123456AB','2022-01-31 00:00:00',NULL),
('12345678','123456AB','2022-08-25 00:00:00',NULL),
('12345678','123456AB','2022-02-22 00:00:00',NULL),
('12345650','123456TW','2022-07-25 00:00:00',NULL),
('22345688','223456PK','2022-01-31 00:00:00',NULL),
('22345688','223456PK','2022-05-25 00:00:00',NULL);



SET @RecNo := 0;
SET @id := '';

drop table if exists cte_idRnk;
create table cte_idRnk
( id varchar(60),
returndate DATETIME ,
idreference VARCHAR(60),
PosNo INTEGER,
id2 varchar(255)

);

INSERT INTO cte_idRnk
(
select t.id,
t.returndate,
t.id_reference,
@RecNo := CASE WHEN @id collate utf8mb4_unicode_ci = t.id then @RecNo + 1 ELSE 1
END AS PosNo,
@id := t.id

from Temp_ReturnTable t
ORDER BY t.id, t.returndate
);

drop table if exists cte_id;
create table cte_id
(
id VARCHAR(60) ,
returndate DATETIME ,
id_reference VARCHAR(60),
lead_auditdate DATETIME
);

INSERT INTO cte_id
(

select ct.id,
ct.returndate,
ct.id_reference,
ct.returndate as lead_auditdate
from cte_idRnk ct
left join cte_idRnk ct2
on ct.id= ct2.id
and ct.PosNo = ct2.PosNo - 1
)
;

update temp_ship s
inner join cte_id r
on s.id = r.id
and ((s.sdt >= r.returndate and r.lead_auditdate is null) OR
(s.sdt >= r.returndate and s.returndate < r.lead_auditdate and r.lead_auditdate is not null))
set id_reference = r.id_reference