Search code examples
mysqlsum

Subtracting or Adding data based on logtime of another table

So currently 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, 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. Suppose the Product with refno. 5 was Publish on 1st October and Sold on 2nd October, The logs table will display as:

Refno status_from status_to logtime
5 Stock Publish 2021-10-01
5 Publish Sold 2021-10-02

This is how my tables currently look like:

Listings table:('D'=>'Draft','N'=>'Action','Y'=>'Publish')

enter image description here

Logs Table which I'm getting using the following statement:

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 crm_logs WHERE logtime < '2021-10-12 00:00:00' ) r 
WHERE r.RN = 1 UNION SELECT refno, logtime, status_from, status_to
FROM crm_logs WHERE logtime <= '2021-10-12 00:00:00' AND logtime >= '2015-10-02 00:00:00'
ORDER BY `refno` ASC

enter image description here

The logs table makes a new record every status change made and passes the current timestamp as the logtime, and the listings table changes/updates the status and updates its update_date. Now to get the total listings as of today I'm using the following statement:

SELECT SUM(status_to = 'D') AS draft, SUM(status_to = 'N') AS action, SUM(status_to = 'Y') AS publish FROM `crm_listings`

And this returns all the count data for status as of the current day.

Now this is where it gets confusing for me. So suppose today the count under action is 10 and tomorrow it'll be 15, and I want to retrieve the total that was present yesterday(10). So for this what I would've to do is take todays total(15) and subtract all the places where a product was changed to draft in between yesterday and today(Total count today in listing table - count(*) where status_to='Action' from logs table). Or vice versa, if yesterday it was 10 under action and today it is 5, it should add the values from the status_from column in logs table

Note: Refno isn't unique in my logs table since a product with the same refno can be marked as publish 1 day and unpublish another, but it is unique in my listings table.

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=01cb3ccfda09f6ddbbbaf02ec92ca894

Solution

  • I am sure it can be simplifed or better. But its my query and logic :

    1. I found status_changes per refno's and calculated total changes from the desired day to present :
    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
    1. I matched the keys between listings table and logs table by converting listings table keys :
    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
    1. I joined them and add the calculations to total sum of current data.
    1. I had to use full outer join , so i have one left and one right join with the same subqueries.

    Lastly I used distinct , since it will generate same result for each joined query and used ifnull to bring the other tables status to the other column .

    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