add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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