1

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

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 for the query to check for transactions in both my tables that happened between today and the date specified in the query and reduce any transaction that have taken after the date that I have specified in my query. If you want an easier explanation for what I'm trying to achieve, please refer to this:

enter image description here

8
  • Nothing to do with your homework, but the model seems to be lacking a foreign key, linking the logs to a specific listing. Oct 18, 2021 at 12:23
  • @GerardH.Pille Alright I have updated the table
    – Jay Modi
    Oct 18, 2021 at 12:39
  • You're mixing 2020-10-01 and 2021-10-01 in your problem description. Is that intentional? Oct 18, 2021 at 13:14
  • @GerardH.Pille Yea, there are basically 2 problems with my current query. 1 is if I select 2021-10-01 my output is wrong since it is not giving my output by checking the logs and listings table like how I've shown on my picture above and the other problem is 2020-10-01 where there are no logs in my logs table, just 1 entry in listings table. So the query should be able to return the output for all initial values too.
    – Jay Modi
    Oct 18, 2021 at 13:20
  • 1
    Have a look at dbfiddle.uk/… Oct 18, 2021 at 13:23

1 Answer 1

1
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;

see db fiddle

5
  • Thanks this worked out. I also have another doubt regarding this if you could kindly check out dba.stackexchange.com/questions/301332/…
    – Jay Modi
    Oct 19, 2021 at 10:58
  • 1
    I'd already noticed it. Interesting problem. If nobody else reacts, I might give it a try. After that, will you come up with something again a bit more complex? Oct 19, 2021 at 12:03
  • Yea, I have another question after this which is a bit more complex than that.
    – Jay Modi
    Oct 19, 2021 at 12:07
  • Why not stop wasting time and give us the full picture immediately? Oct 19, 2021 at 14:05
  • No so I've been told to make 3 tables for my reports page, 1 with total on a particular date, 1 with filter between 2 date ranges and another table with referencing it from an agent_id
    – Jay Modi
    Oct 19, 2021 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.