MySQL 8.0

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

select version();


version()


8.0.27


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, 'Y','2021-05-02','2021-10-02','LP01'), (4, 'A','2021-05-01','2021-05-01','LP02'),
 (5, 'S','2020-10-01','2020-10-01','LP03'), (6, 'N','2021-05-01','2021-10-06','LP06'), 
 (10, 'D','2021-10-06','2021-10-06','LP05'), (11, 'D','2021-01-01','2021-01-01','LP04');
select * from listings;


id
status
added_date
updated_date
refno


3
Y
2021-05-02 00:00:00
2021-10-02 00:00:00
LP01

4
A
2021-05-01 00:00:00
2021-05-01 00:00:00
LP02

5
S
2020-10-01 00:00:00
2020-10-01 00:00:00
LP03

6
N
2021-05-01 00:00:00
2021-10-06 00:00:00
LP06

10
D
2021-10-06 00:00:00
2021-10-06 00:00:00
LP05

11
D
2021-01-01 00:00:00
2021-01-01 00:00:00
LP04


INSERT INTO logs VALUES
 (1, 'N','A','2021-06-01','LP01'), (2, 'A','D','2021-10-01','LP01'), 
 (3, 'D','Y','2021-10-02','LP01'), (4, 'A','N','2021-10-06','LP06');
select * from logs


id
status_from
status_to
logtime
refno


1
N
A
2021-06-01 00:00:00
LP01

2
A
D
2021-10-01 00:00:00
LP01

3
D
Y
2021-10-02 00:00:00
LP01

4
A
N
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
Y
2021-05-02 00:00:00
2021-10-02 00:00:00
LP01
A
A

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

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

6
N
2021-05-01 00:00:00
2021-10-06 00:00:00
LP06
A
A

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


create table status (code varchar(1), text varchar(100));
insert into status (code, text) 
values ('D', 'Draft'), ('A', 'Action'), ('Y', 'Publish'), ('S', 'Sold'), ('N', 'Let');
with X as (
  select 
    l.*,
    (select status_from from logs where logs.refno = l.refno and logs.logtime >= '2021-10-02' order by logs.logtime limit 1) logstat
  from listings l
  where l.added_date between '2020-10-01 00:00:00' and '2021-10-02 00:00:00'
)
, Y as (select X.*, ifnull(X.logstat, X.status) stat20211001 from X)
SELECT * from Y;


id
status
added_date
updated_date
refno
logstat
stat20211001


3
Y
2021-05-02 00:00:00
2021-10-02 00:00:00
LP01
D
D

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

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

6
N
2021-05-01 00:00:00
2021-10-06 00:00:00
LP06
A
A

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


with X as (
  select 
    l.*,
    (select status_from from logs where logs.refno = l.refno and logs.logtime >= '2021-10-02' order by logs.logtime limit 1) logstat
  from listings l
  where l.added_date between '2020-10-01 00:00:00' and '2020-10-02 00:00:00'
)
, Y as (select X.*, ifnull(X.logstat, X.status) stat20211001 from X)
SELECT 
  added_date,
  -- status.text,
  COUNT(CASE WHEN status.text = 'Publish' THEN Y.id END) AS Publish,
  COUNT(CASE WHEN status.text = 'Action' THEN Y.id END) AS Action,
  COUNT(CASE WHEN status.text = 'Let' THEN Y.id END) AS Let,
  COUNT(CASE WHEN status.text = 'Sold' THEN Y.id END) AS Sold,
  COUNT(CASE WHEN status.text = 'Draft' THEN Y.id END) AS Draft
from status
join Y on Y.stat20211001 = status.code
group by added_date;


added_date
Publish
Action
Let
Sold
Draft


2020-10-01 00:00:00
0
0
0
1
0

id	status	added_date	updated_date	refno
3	Y	2021-05-02 00:00:00	2021-10-02 00:00:00	LP01
4	A	2021-05-01 00:00:00	2021-05-01 00:00:00	LP02
5	S	2020-10-01 00:00:00	2020-10-01 00:00:00	LP03
6	N	2021-05-01 00:00:00	2021-10-06 00:00:00	LP06
10	D	2021-10-06 00:00:00	2021-10-06 00:00:00	LP05
11	D	2021-01-01 00:00:00	2021-01-01 00:00:00	LP04

id	status_from	status_to	logtime	refno
1	N	A	2021-06-01 00:00:00	LP01
2	A	D	2021-10-01 00:00:00	LP01
3	D	Y	2021-10-02 00:00:00	LP01
4	A	N	2021-10-06 00:00:00	LP06