clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (40635 in the last week).

select version();
version()
8.0.27
 hidden batch(es)


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) );
 hidden batch(es)


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) );
 hidden batch(es)


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');
 hidden batch(es)


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');
 hidden batch(es)


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
 hidden batch(es)


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 A
5 S 2020-10-01 00:00:00 2020-10-01 00:00:00 LP03 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 D
 hidden batch(es)


create table status (code varchar(1), text varchar(100));
 hidden batch(es)


insert into status (code, text) values ('D', 'Draft'), ('A', 'Action'), ('Y', 'Publish'), ('S', 'Sold'), ('N', 'Let');
 hidden batch(es)


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 < '2021-10-02' ) , Y as (select X.*, ifnull(X.logstat, X.status) stat20211001 from X) SELECT status.text, COUNT(Y.id) AS c from status left join Y on Y.stat20211001 = status.code group by status.code, status.text;
text c
Draft 2
Action 2
Publish 0
Sold 1
Let 0
 hidden batch(es)