MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.26


CREATE TABLE listings
(
  id INTEGER NOT NULL PRIMARY KEY,
  status VARCHAR (50),
  added_date datetime,
  updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  refno VARCHAR (50),
  UNIQUE (refno)
);
CREATE TABLE logs
(
  id INTEGER NOT NULL PRIMARY KEY,
  status_from VARCHAR (50),
  status_to  VARCHAR (50),
  logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  refno VARCHAR (50),
  FOREIGN KEY (refno) REFERENCES listings(refno),
  unique (refno, logtime)
);
INSERT INTO listings VALUES
 (3, 'Publish','2021-05-02','2021-10-02','LP01'), (4, 'Action','2021-05-01','2021-05-01','LP02'),
 (5, 'Sold','2020-10-01','2020-10-01','LP03'), (6, 'Let','2021-05-01','2021-10-06','LP06'), 
 (10, 'Draft','2021-10-06','2021-10-06','LP05'), (11, 'Draft','2021-01-01','2021-01-01','LP04');
INSERT INTO logs VALUES
 (1, 'Let','Action','2021-06-01','LP01'), (2, 'Action','Draft','2021-10-01','LP01'), 
 (3, 'Draft','Publish','2021-10-02','LP01'), (4, 'Action','Let','2021-10-06','LP06');
select * from logs


id
status_from
status_to
logtime
refno


1
Let
Action
2021-06-01 00:00:00
LP01

2
Action
Draft
2021-10-01 00:00:00
LP01

3
Draft
Publish
2021-10-02 00:00:00
LP01

4
Action
Let
2021-10-06 00:00:00
LP06


with X as (
  select l.*,
         (select status_from from logs where logs.refno = l.refno and logs.logtime >= '2021-10-01' order by logs.logtime limit 1) logstat
    from listings l
    where l.added_date < '2021-10-01')
select X.*, ifnull(X.logstat,X.status) stat20211001 from X;


id
status
added_date
updated_date
refno
logstat
stat20211001


3
Publish
2021-05-02 00:00:00
2021-10-02 00:00:00
LP01
Action
Action

4
Action
2021-05-01 00:00:00
2021-05-01 00:00:00
LP02
null
Action

5
Sold
2020-10-01 00:00:00
2020-10-01 00:00:00
LP03
null
Sold

6
Let
2021-05-01 00:00:00
2021-10-06 00:00:00
LP06
Action
Action

11
Draft
2021-01-01 00:00:00
2021-01-01 00:00:00
LP04
null
Draft

id	status_from	status_to	logtime	refno
1	Let	Action	2021-06-01 00:00:00	LP01
2	Action	Draft	2021-10-01 00:00:00	LP01
3	Draft	Publish	2021-10-02 00:00:00	LP01
4	Action	Let	2021-10-06 00:00:00	LP06

id	status	added_date	updated_date	refno	logstat	stat20211001
3	Publish	2021-05-02 00:00:00	2021-10-02 00:00:00	LP01	Action	Action
4	Action	2021-05-01 00:00:00	2021-05-01 00:00:00	LP02	null	Action
5	Sold	2020-10-01 00:00:00	2020-10-01 00:00:00	LP03	null	Sold
6	Let	2021-05-01 00:00:00	2021-10-06 00:00:00	LP06	Action	Action
11	Draft	2021-01-01 00:00:00	2021-01-01 00:00:00	LP04	null	Draft