Returning data count from a particular day after comparing 2 tables

[JJM50]

Here is the dbfiddle for better understanding, refer this when reading question: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0919cacb5d117450168cdc917433a45e

I have 2 tables called listings and logs table. The listings table holds a products reference number and it's current status. So suppose if it's status was Publish currently and it's sold later, the status updates to Sold. Here the refno. in this table is unique since the status can change for 1 product.

Now I have another table called Logs table, this table records all the status changes that have happened for a particular product(referenced by refno) in a particular timeframe. The initial entry in the listings table is not recorded here, but once it's status is changed, that entry is shown here.

Suppose I have the following Listings table('D' => 'Draft', 'A' => 'Action', 'Y' => 'Publish', 'S' => 'Sold', 'N' => 'Let'):

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');

Here as of now the total count under every status would give:

|status_1|c|
|:---:|:--:|
|Publish|1|
|Action|1|
|Sold|1|
|Left|1|
|Draft|2|

But if I wanted only the count for entries made in 2020-10-01 it'll show 0 under all statuses except sold, where it'll show 1.

Now in this timeframe between 2020-10-01 and today, there have been values entered in listings table as shown above and also for some, the statuses have changed. Status table:

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');

What is being shown right now in my listings table is the values after the status change has been made. So now to get the total count on a particular day, I'm having my statement reference the dates from the logs table and respectively subtract the status_to, and add the status_from. Query for this is in the dbfiddle provided above. Here I made it to return data that happened on or before 2021-10-01 and it does not give the right output. 

Another problem with this query is I cannot return the data for the entries that had initially taken place. For example like I mentioned above the value for the data on 2020-10-01 should show 1 under sold, while 0 under everything else(desired output), but it does not do this since there are no logs made in logs table for when a new entry in initially added.

So basically what I want here is to get the initial entries as well with the same entry. If you want an easier explanation for what I'm trying to achieve, please refer to this: 

[requinix]

The Let/Action/Draft/Publish thing is a different concept than the Chocolate/Candy thing. Giving the latter as an example only makes the former more confusing.

Are you trying to get the status of each thing as it was on a particular date? Wouldn't that mean that the status of each thing is effectively the most recent "status_to" value you have?

[JJM50]

11 hours ago, requinix said:

The Let/Action/Draft/Publish thing is a different concept than the Chocolate/Candy thing. Giving the latter as an example only makes the former more confusing.

Are you trying to get the status of each thing as it was on a particular date? Wouldn't that mean that the status of each thing is effectively the most recent "status_to" value you have?

Okay so yes I am trying to get the status on a particular date. Now the reason it won't be the most recent status_to is that For every new entry made in my listings table, that doesn't show up in my logs table. All initial entries are not brought up in the logs table, only once the status for that listing is changed by someone, the entry for that listing is brought up in the logs table.

Also let me make the example a bit easier by refering to the dbfiddle I've given: