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

select version();
version()
8.0.26
 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) );
 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) );
 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, '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');
 hidden batch(es)


SELECT refno, logtime, status_from, status_to FROM ( SELECT refno, logtime, status_from, status_to, ROW_NUMBER() OVER(PARTITION BY refno ORDER BY logtime DESC) AS RN FROM logs WHERE logtime < '2021-10-12 00:00:00' ) r WHERE r.RN = 1 UNION SELECT refno, logtime, status_from, status_to FROM logs WHERE logtime <= '2021-10-12 00:00:00' AND logtime >= '2015-10-02 00:00:00' ORDER BY `refno` ASC
refno logtime status_from status_to
LP01 2021-10-02 00:00:00 Draft Publish
LP01 2021-06-01 00:00:00 Let Action
LP01 2021-10-01 00:00:00 Action Draft
LP06 2021-10-06 00:00:00 Action Let
 hidden batch(es)


select case status when 'D' THEN 'Draft' when 'A' THEN 'Action' when 'Y' THEN 'Publish' when 'S' THEN 'Sold' when 'N' THEN 'Let' END status_l ,COUNT(*) c from listings group by status
status_l c
Publish 1
Action 1
Sold 1
Let 1
Draft 2
 hidden batch(es)


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


select status_logs, sum(cnt_status) to_add from ( SELECT status_to as status_logs, -1*count(*) cnt_status FROM logs lm where id = (select max(id) from logs l where l.refno = lm.refno) and logtime >= '2021-10-02 00:00:00' group by status_to union all SELECT status_from, count(*) cnt_status_from FROM logs lm where id = (select max(id) from logs l where l.refno = lm.refno) and logtime >= '2021-10-02 00:00:00' group by status_from ) total_changes group by status_logs
status_logs to_add
Publish -1
Let -1
Draft 1
Action 1
 hidden batch(es)


select distinct IFNULL(status_l,status_logs) status ,counts_at_2021_10_01 from ( select l.*,logs.* , l.c + ifnull(logs.to_add,0) counts_at_2021_10_01 from (select case status when 'D' THEN 'Draft' when 'A' THEN 'Action' when 'Y' THEN 'Publish' when 'S' THEN 'Sold' when 'N' THEN 'Let' END status_l ,COUNT(*) c from listings group by status) l left join ( select status_logs, sum(cnt_status) to_add from ( SELECT status_to as status_logs, -1*count(*) cnt_status FROM logs lm where id = (select max(id) from logs l where l.refno = lm.refno) and logtime >= '2021-10-01 00:00:00' group by status_to union all SELECT status_from, count(*) cnt_status_from FROM logs lm where id = (select max(id) from logs l where l.refno = lm.refno) and logtime >= '2021-10-01 00:00:00' group by status_from ) total_changes group by status_logs ) logs on logs.status_logs = l.status_l union all select l.*,logs.* , l.c + ifnull(logs.to_add,0) counts_at_2021_05_01 from (select case status when 'D' THEN 'Draft' when 'A' THEN 'Action' when 'Y' THEN 'Publish' when 'S' THEN 'Sold' when 'N' THEN 'Let' END status_l ,COUNT(*) c from listings group by status) l right join ( select status_logs, sum(cnt_status) to_add from ( SELECT status_to as status_logs, -1*count(*) cnt_status FROM logs lm where id = (select max(id) from logs l where l.refno = lm.refno) and logtime >= '2021-10-01 00:00:00' group by status_to union all SELECT status_from, count(*) cnt_status_from FROM logs lm where id = (select max(id) from logs l where l.refno = lm.refno) and logtime >= '2021-10-01 00:00:00' group by status_from ) total_changes group by status_logs ) logs on logs.status_logs = l.status_l ) l
status counts_at_2021_10_01
Publish 0
Action 2
Sold 1
Let 0
Draft 3
 hidden batch(es)