MySQL Forums
Forum List  »  Newbie

Find the closest previous date and update matching records from another table
Posted by: Joni villa
Date: August 19, 2022 12:27AM

Hi,

I'm looking to find the closest previous sdt from returndate and get the associated id_reference by idno and update the id_reference in @sampleship table. id_reference is a unique number combinateion of idno and returndate.

I have a query that I made in TSQL, any help to convert this to mySQL to fit in my requirements or is there any other alternative approach.

I'm using MySQL Workbench 8.0 CE

Below is a sample DDL and desired result. Thank you in advance.

create table sampleReturns(idno varchar(35),id varchar(35), returndate
datetime, id_reference varchar(60))
insert into sampleReturns (idno,id, returndate, id_reference )
values ('12345670','123456PH','2022-04-26 00:00:00','2022-04-26 00:00:0012345670'),
('12345678','123456AB','022-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 @sampleship table(idno varchar(35),id varchar(35), sdt datetime, id_reference varchar(60))
insert into @sampleship table (idno,into, 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-02-25 00:00:00',NULL),
('22345688','223456PK','2022-01-31 00:00:00',NULL),
('22345688','223456PK','2022-05-25 00:00:00',NULL)

Desired result
@sampleship table
+---------+---------+-------------------+---------------------------+
|idno | id | sdt | id_reference |
+---------+---------+-------------------+---------------------------+
|12345670 |123456PH |2020-11-26 00:00:00|NULL |
|12345670 |123456PH |2022-04-23 00:00:00|2022-04-26 00:00:0012345670|
|12345670 |123456PH |2022-07-25 00:00:00|NULL |
|12345678 |123456AB |2022-01-31 00:00:00|2022-02-18 00:00:0012345678|
|12345678 |123456AB |2022-08-25 00:00:00|2022-05-25 00:00:0012345678|
|12345678 |123456AB |2022-02-22 00:00:00|NULL |
|12345650 |123456TW |2022-02-25 00:00:00|2022-03-01 00:00:0012345650|
|22345688 |223456PK |2022-01-31 00:00:00|2022-03-08 00:00:0022345688|
|22345688 |223456PK |2022-05-25 00:00:00|NULL |
+---------+---------+-------------------+---------------------------+

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.