Jump to content

Returning data count from a particular day after comparing 2 tables


JJM50
 Share

Recommended Posts

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: 

image.thumb.png.266f445ece09db9d73ccb59b7147e582.png

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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:

image.png.3bb5bbf19df4bb35efaaec84dbdfeb83.png

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.